|
实在对不住,前天本菜鸟小试射手,刚好写了这么一段代码。现成的。分享给你用一下,记着,我要鲜花。先发代码,也会发出附件。
- Sub 拆分总表()
- Sheets(1).Select
- Ro = [A65536].End(3).Row '根据第A列提取最大行数
- Co = [XFD1].End(1).Column '根据第1行提取最大列数
- Dim i As Integer, sht As Worksheet
- m = InputBox("按第几列拆分?" & Chr(10) & Chr(10) & "列序数范围:1 至 " & Co & "")
- '退出机制
- If Not IsNumeric(m) Then
- MsgBox "你输入的不是数字!"
- Exit Sub
- ElseIf m * 1 > Co Then
- MsgBox "超出了数据的列数!"
- Exit Sub
- End If
- '删除分表
- Call 删除分表
- '创建新表
- For i = 2 To Ro
- k = 0
- For Each sht In Sheets
- If sht.Name = Sheets(1).Cells(i, m * 1) Then
- k = 1
- Exit For
- End If
- Next
- If k = 0 Then
- Sheets.Add(after:=Sheets(Sheets.Count)).Name = Sheets(1).Cells(i, m * 1)
- End If
- Next
- '拷贝数据
- For i = 2 To Sheets.Count
- Sheets(1).[A1].Resize(Ro, Co).AutoFilter Field:=m, Criteria1:=Sheets(i).Name
- Sheets(1).[A1].Resize(Ro, Co).Copy Sheets(i).[A1]
- Next
- Sheets(1).[A1].Resize(Ro, Co).AutoFilter
- Sheets(1).Select
- MsgBox "操作完毕!"
- End Sub
- Sub 删除分表()
- Dim sht As Worksheet
- Application.DisplayAlerts = False
- For Each sht In Sheets
- If sht.Name <> Sheets(1).Name Then
- sht.Delete
- End If
- Next
- Application.DisplayAlerts = True
- End Sub
复制代码
|
评分
-
1
查看全部评分
-
|