以上代码编译时提示缺少数组的错误
时间: 2024-03-06 08:51:31 浏览: 17
非常抱歉,我之前的代码有误。以下是一个修正后的版本:
```VBA
Sub MergeWorksheets()
Dim path() As Variant
Dim i As Long
Dim currentWB As Workbook, targetWB As Workbook
Dim currentWS As Worksheet, targetWS As Worksheet
Dim currentRow As Long, lastRow As Long
'选择目标工作簿
Set targetWB = Application.Workbooks.Open("C:\TargetWorkbook.xlsx")
'选择源工作簿所在文件夹
path = Application.GetOpenFilename(filefilter:="Excel Files (*.xls*),*.xls*", Title:="Select Files to Merge", MultiSelect:=True)
'循环遍历每个源工作簿中的每个工作表
For i = LBound(path) To UBound(path)
Set currentWB = Application.Workbooks.Open(path(i))
For Each currentWS In currentWB.Worksheets
'在目标工作簿中添加新工作表
Set targetWS = targetWB.Worksheets.Add(After:=targetWB.Worksheets(targetWB.Worksheets.Count))
'将当前工作表的数据复制到目标工作簿的新工作表中
currentWS.UsedRange.Copy targetWS.Range("A1")
'调整目标工作表的格式
With targetWS
.Cells.EntireColumn.AutoFit
lastRow = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
.ListObjects.Add(xlSrcRange, Range("A1:D" & lastRow), , xlYes).Name = "Table1"
.ListObjects("Table1").TableStyle = "TableStyleMedium2"
End With
Next currentWS
'关闭源工作簿
currentWB.Close False
Next i
'保存目标工作簿
targetWB.Save
'关闭目标工作簿
targetWB.Close
End Sub
```
请注意,我已将“path”声明为数组,并用“Variant”来定义它。这样,您就可以正确地使用多个源文件来运行宏了。