|
sheets("录入表")是母工作表,希望按照A列的序号拆分工作表. | 并且以后会经常按照顺序在下面添加客户,格式都不改变的, | 拆分后的工作表格式如后边样表的格式 | 想做个按钮,每次添加客户之后,点下按钮就会自动生产以这个客户序号(A列)为工作表的名字 | 并且C列的公司名字能创建个超链接,点击能自动跳转到改公司所在的工作表 |
- Sub 生成新表()
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1:k" & Sheet1.[a65536].End(3).Row)
- For i = 3 To UBound(arr)
- d(Val(arr(i, 1))) = i
- Next
- a = arr(UBound(arr), 1) '最末行序号(默认为以此序号新建表)
- xh = InputBox("请输入要生成表式的序号", , a)
- For Each sh In Worksheets
- x = x & "," & sh.Name
- Next
- If InStr("," & x, "," & xh & ",") Then MsgBox "工作表" & xh & "已存在": Exit Sub
- r = d(Val(xh)) '根据序号找到对应行r
- If r = 0 Then MsgBox "序号" & xh & "不存在": Exit Sub
- Sheets("表式").Copy after:=Sheets(Sheets.Count)
- With ActiveSheet
- .Name = xh
- .[a1] = arr(r, 3) '公司名
- .[b2] = xh '序号
- .[b3].Resize(4, 1) = Application.Transpose(Array(arr(r, 5), arr(r, 7), arr(r, 6), arr(r, 4))) '左四
- .[f3].Resize(4, 1) = Application.Transpose(Array(arr(r, 8), arr(r, 9), arr(r, 11), arr(r, 10))) '右四
- End With
- Sheet1.Activate
- Sheet1.Hyperlinks.Add Sheet1.Cells(r, 3), Address:="", SubAddress:="'" & Sheets(xh).Name & "'" & "!A1" 'r行第三列加超链接
- End Sub
复制代码
|
|