excle表格如何将数据拆分成不通的sheet页
这篇文章给大家介绍excle表格如何将数据拆分成不通的sheet页,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。
方城ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景,ssl证书未来市场广阔!成为创新互联的ssl证书销售渠道,可以享受市场价格4-6折优惠!如果有意向欢迎电话联系或者加微信:18982081108(备注:SSL证书合作)期待与您的合作!
13先展示最后效果:
开始步骤例子:
代码如下
Sub CFGZB() Dim myRange As Variant Dim myArray Dim titleRange As Range Dim title As String Dim columnNum As Integer myRange = Application.InputBox(prompt:="请选择标题行:", Type:=8) myArray = WorksheetFunction.Transpose(myRange) Set titleRange = Application.InputBox(prompt:="请选择拆分的表头,必须是第一行,且为一个单元格,如:“姓名”", Type:=8) title = titleRange.Value columnNum = titleRange.Column Application.ScreenUpdating = False Application.DisplayAlerts = False Dim i&, Myr&, Arr, num& Dim d, k For i = Sheets.Count To 1 Step -1 If Sheets(i).Name <> "Sheet1" Then Sheets(i).Delete End If Next i Set d = CreateObject("Scripting.Dictionary") Myr = Worksheets("Sheet1").UsedRange.Rows.Count Arr = Worksheets("Sheet1").Range(Cells(2, columnNum), Cells(Myr, columnNum)) For i = 1 To UBound(Arr) d(Arr(i, 1)) = "" Next k = d.keys For i = 0 To UBound(k) Set conn = CreateObject("adodb.connection") conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName Sql = "select * from [Sheet1$] where " & title & " = '" & k(i) & "'" Worksheets.Add after:=Sheets(Sheets.Count) With ActiveSheet .Name = k(i) For num = 1 To UBound(myArray) .Cells(1, num) = myArray(num, 1) Next num .Range("A2").CopyFromRecordset conn.Execute(Sql) End With Sheets(1).Select Sheets(1).Cells.Select Selection.Copy Worksheets(Sheets.Count).Activate ActiveSheet.Cells.Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Next i conn.Close Set conn = Nothing Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107
6.
7.
8.
9.
10
11.
12注意:
1)原始数据表要从第一行开始有数据,并且不能有合并单元格;
2)打开工作簿时需要开启宏,否则将无法运行代码。
2、生成目录
a、新建一个sheet,名字改为”目录” 在开发工具中
Sub createmenu()
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
'Cells(i, 2) = Sheets(i).Cells(2, 2).Value
Next i
End Sub
双击下目录那个sheet 粘贴并执行;
b、增加超链接
在sheet1中B2单元格中定义超链接函数,录入=HYPERLINK("#"&A2&"!A1",A2)
c、增加返回目录
选中所有sheet(目录除外)在任意一张中选个空白的单元格输入 =HYPERLINK(“#目录!A1”,”返回目录”)
关于excle表格如何将数据拆分成不通的sheet页就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
分享文章:excle表格如何将数据拆分成不通的sheet页
地址分享:http://pwwzsj.com/article/jcjspg.html