案例
题目:
excel将下面叫做“数据”的工作表根据分类条件拆分成多个sheet工作表,表名为分类条件。
结果如下:
分析:
注意:执行这段代码前必须选中总表即你要拆分的表,否则数据会遭到破坏
代码:
Sub chaifen()
Dim i As Integer
Dim j, k, irow, count As Integer
Dim sht As Worksheet
Dim sht1 As Worksheet
Dim x As Integer
Dim sht0 As Worksheet
Set sht0 = ActiveSheet
x = InputBox("请选择你要按哪列分,第几列就填几")
'执行分表前删除多余的表
Application.DisplayAlerts = False
If Sheets.count > 1 Then
For Each sht1 In Sheets
If sht1.Name <> sht0.Name Then
sht1.Delete
End If
Next
End If
Application.DisplayAlerts = True
'获取sheet1总行数
irow = sht0.Range("a65536").End(xlUp).Row
For i = 2 To irow
'初始化k
k = 0
For Each sht In Sheets
'判断是否已存在表名
If sht.Name = sht0.Cells(i, x) Then
k = 1
End If
Next
'如果不存在表名就新建一个表
If k = 0 Then
Sheets.Add after:=Sheets(Sheets.count)
Sheets(Sheets.count).Name = sht0.Cells(i, x)
End If
'筛选拷贝数据
For j = 2 To Sheets.count
sht0.Range("a1:f" & irow).AutoFilter field:=x, Criteria1:=Sheets(j).Name
sht0.Range("a1:f" & irow).Copy Sheets(j).Range("a1")
'关闭筛选
sht0.Range("a1:f" & irow).AutoFilter
Next
Next
sht0.Select
End Sub
注意:执行这段代码前必须选中总表即你要拆分的表,否则数据会遭到破坏
这段案例代码的知识点有:
联系客服