打开文件的路径参数你需要自己修改下,C:\Users\Fanyoulin\Downloads\test是我的路径
If Cells([a100000].End(3).Row, 1) > 3 Then
Range(Cells(4, 1), Cells([a100000].End(3).Row, 1)).ClearContents
End If
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Users\Fanyoulin\Downloads\test\testfile.xlsx")
hs = 4
For i = 1 To wb.Worksheets.Count
For k = 1 To 3
s = IIf(k = 1, 2, IIf(k = 2, 7, 12))
s1 = wb.Worksheets(i).Cells(65000, s).End(3).Row
If s1 > 4 Then
For j = 4 To s1
ThisWorkbook.Worksheets(1).Cells(hs, 1) = wb.Worksheets(i).Cells(j, s)
hs = hs + 1
Next j
End If
Next k
Next i
wb.Close
Application.ScreenUpdating = True
For k = 1 To 7
s = IIf(k=1, 2,IIf(k=2, 5,iif(k=3,8,iif(k=4,11,iif(k=5,14,iif(k=6,17,20))))))
这行代码我没实测,写法与工作表函数if的嵌套是一个意思,如果有错你自己修改下就行了。其他不用改。
k是列数,总共有7列需要执行操作,也就是需要执行7次列循环;
s是具体的列位置,由k来确定具体的列在哪里。k=1,s是第2列,k=2,s就是第5列.....