1 Sub link()
2 Dim num, sheetname
3
4 Worksheets(1).Select
5
6 num = WorksheetFunction.CountA(Columns("c:c"))
7 'MsgBox num
8
9 For i = 2 To num
10 '把第一个sheet中第3列第i行单元格的值赋值给sheetname,作为后面创建sheet时的名称
11 sheetname = VBA.UCase(Trim(Sheets(1).Cells(i, 3)))
12
13 '用单元格的值作为sheet名创建sheet
14 On Error Resume Next
15 Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sheetname
16
17 '在新建的sheet中,给A1单元格输入“返回”字符串
18 Sheets(sheetname).Cells(1, 1) = "返回"
19
20 '将新建的sheet中,返回字符串所在单元格创建链接,链接地址是第一个sheet中第3列,第i行单元格
21 Sheets(sheetname).Hyperlinks.Add Anchor:=Sheets(sheetname).Cells(1, 1), Address:="", SubAddress:="汇总!C" & i
22
23 '在新建的sheet中添加固定格式
24 Sheets(sheetname).Cells(2, 1) = Trim(Sheets(1).Cells(i, 1)) + ".1 表" + sheetname
25 Sheets(sheetname).Cells(2, 1).Font.FontStyle = "加粗"
26
27 Sheets(sheetname).Cells(3, 1) = Trim(Sheets(1).Cells(i, 1)) + ".1.1 表" + sheetname + "的卡片"
28 Sheets(sheetname).Cells(3, 1).Font.FontStyle = "加粗"
29
30 Sheets(sheetname).Cells(4, 1) = "名称"
31 Sheets(sheetname).Cells(4, 1).Interior.Color = RGB(153, 204, 255)
32 Sheets(sheetname).Cells(4, 1).Font.FontStyle = "加粗"
33 Sheets(sheetname).Cells(4, 1).Borders.LineStyle = xlContinuous
34
35 Sheets(sheetname).Cells(4, 2) = VBA.UCase(Trim(Sheets(1).Cells(i, 2)))
36 Sheets(sheetname).Cells(4, 2).Interior.Color = RGB(255, 255, 204)
37 Sheets(sheetname).Cells(4, 2).Borders.LineStyle = xlContinuous
38
39
40 Sheets(sheetname).Cells(5, 1) = "代码"
41 Sheets(sheetname).Cells(5, 1).Interior.Color = RGB(153, 204, 255)
42 Sheets(sheetname).Cells(5, 1).Font.FontStyle = "加粗"
43 Sheets(sheetname).Cells(5, 1).Borders.LineStyle = xlContinuous
44
45
46 Sheets(sheetname).Cells(5, 2) = sheetname
47 Sheets(sheetname).Cells(5, 2).Interior.Color = RGB(255, 255, 204)
48 Sheets(sheetname).Cells(5, 2).Borders.LineStyle = xlContinuous
49
50
51 Sheets(sheetname).Cells(6, 1) = "注释"
52 Sheets(sheetname).Cells(6, 1).Interior.Color = RGB(153, 204, 255)
53 Sheets(sheetname).Cells(6, 1).Font.FontStyle = "加粗"
54 Sheets(sheetname).Cells(6, 1).Borders.LineStyle = xlContinuous
55
56
57 Sheets(sheetname).Cells(6, 2) = Trim(Sheets(1).Cells(i, 4))
58 Sheets(sheetname).Cells(6, 2).Interior.Color = RGB(255, 255, 204)
59 Sheets(sheetname).Cells(6, 2).Borders.LineStyle = xlContinuous
60
61
62 Sheets(sheetname).Cells(8, 1) = Trim(Sheets(1).Cells(i, 1)) + ".1.2 表" + sheetname + "的字段清单"
63 Sheets(sheetname).Cells(8, 1).Font.FontStyle = "加粗"
64
65 Sheets(sheetname).Cells(9, 1) = "名称"
66 Sheets(sheetname).Cells(9, 1).Interior.Color = RGB(153, 204, 255)
67 Sheets(sheetname).Cells(9, 1).Font.FontStyle = "加粗"
68 Sheets(sheetname).Cells(9, 1).Borders.LineStyle = xlContinuous
69
70 Sheets(sheetname).Cells(9, 2) = "代码"
71 Sheets(sheetname).Cells(9, 2).Interior.Color = RGB(153, 204, 255)
72 Sheets(sheetname).Cells(9, 2).Font.FontStyle = "加粗"
73 Sheets(sheetname).Cells(9, 2).Borders.LineStyle = xlContinuous
74
75 Sheets(sheetname).Cells(9, 3) = "注释"
76 Sheets(sheetname).Cells(9, 3).Interior.Color = RGB(153, 204, 255)
77 Sheets(sheetname).Cells(9, 3).Font.FontStyle = "加粗"
78 Sheets(sheetname).Cells(9, 3).Borders.LineStyle = xlContinuous
79
80 Sheets(sheetname).Cells(9, 4) = "类型"
81 Sheets(sheetname).Cells(9, 4).Interior.Color = RGB(153, 204, 255)
82 Sheets(sheetname).Cells(9, 4).Font.FontStyle = "加粗"
83 Sheets(sheetname).Cells(9, 4).Borders.LineStyle = xlContinuous
84
85 Sheets(sheetname).Cells(9, 5) = "能否为空"
86 Sheets(sheetname).Cells(9, 5).Interior.Color = RGB(153, 204, 255)
87 Sheets(sheetname).Cells(9, 5).Font.FontStyle = "加粗"
88 Sheets(sheetname).Cells(9, 5).Borders.LineStyle = xlContinuous
89
90 Sheets(sheetname).Cells(9, 6) = "默认值"
91 Sheets(sheetname).Cells(9, 6).Interior.Color = RGB(153, 204, 255)
92 Sheets(sheetname).Cells(9, 6).Font.FontStyle = "加粗"
93 Sheets(sheetname).Cells(9, 6).Borders.LineStyle = xlContinuous
94
95
96 'MsgBox """" & sheetname & "!A2"""
97 'MsgBox Sheets(1).Cells(i, 3)
98
99 '在第一个sheet中第3列,第i行添加链接,链接地址是第i个sheet的A1单元格
100 Sheets(1).Hyperlinks.Add Anchor:=Sheets(1).Cells(i, 3), Address:="", SubAddress:=sheetname & "!A1"
101
102 Next
103
104 End Sub