将所有工作表复制到新工作簿时发生Excel运行时错误

Question

excel vba非常新，只是想问我为什么在with-wb上出现运行时错误。纸张("i"(行。它应该如何工作，它应该打开文件并将所有6个工作表复制到指定的工作簿中。with函数试图只获取和复制第一个工作表的页眉，而不复制下一个工作表上的页眉。

Sub Button1_Click()
Dim i As Long
Dim lcurrow As Long
Dim lrow As Long
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Open("/Users/Documents/2020-06-22.xlsx")
For i = 1 To 6 Step 1
With wb.Sheets(i)
If i = 1 Then
lrow = 1
Else
lrow = 2
End If

Do Until .Range("A" & lrow).Value = vbNullString
lcurrow = lcurrow + 1
For n = 0 To 3 Step 1
wb.Sheets(i).Range("A" & lcurrow).Offset(columnoffset:=n).Value = .Range("A" & lrow).Offset(columnoffset:=n).Value
Next n
lrow = lrow + 1
Loop

End With

Next i
Set wb = Nothing
End Sub

Answer 1

如果您想循环浏览工作簿中的所有工作表，可以执行

For each ws in wb.sheets
'your code you want to do on each worksheet here
Next ws