好友
阅读权限10
听众
最后登录1970-1-1
|
VBA小白
发表于 2021-2-20 10:31
本帖最后由 VBA小白 于 2021-2-20 11:58 编辑
Sub 拆分数据表()
Dim i As Integer
Dim irow As Integer
Dim l As Integer
Dim j As Integer
Dim sht As Worksheet
Dim m As Integer
Dim t As Integer
l = InputBox("请输入要划分的列数(数字),例如:1")
Application.DisplayAlerts = False
For m = Sheets.Count To 2 Step -1
Sheets(m).Delete
Next
Application.DisplayAlerts = True
irow = Sheet1.Range("a65536").End(xlUp).Row
'a列的最后行
For i = 2 To irow
k = 0
For Each sht In Sheets
If sht.Name = Sheet1.Cells(i, l) Then
k = 1
End If
Next
If k = 0 Then
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheet1.Cells(i, l)
End If
Next
For j = 2 To Sheets.Count
t = Range("a1").End(xlToRight).Column
'是第1行的最后列数,但是这里t显示是16384,不知道为什么
Sheet1.Range(Cells(1, 1), Cells(irow, t)).AutoFilter Field:=l, Criteria1:=Sheets(j).Name
Sheet1.Range(Cells(1, 1), Cells(irow, t)).Copy Sheets(j).Range("a1")
Next
Sheet1.Range(Cells(1, 1), Cells(irow, t)).AutoFilter
MsgBox "拆分完成"
End Sub
复制粘贴的应该是Range(Cells(1, 1), Cells(irow, t)),但是运行后每张表都粘贴了从1到irow整行
|
-
-
复制粘贴的应该是range("a1:d22")
|
发帖前要善用【论坛搜索】功能,那里可能会有你要找的答案或者已经有人发布过相同内容了,请勿重复发帖。 |
|
|
|
|