13518219792
这篇文章给大家介绍excle表格如何将数据拆分成不通的sheet页，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
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 Sub1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061076.
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页就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
