用VBA复制多个excel的sheet1
时间: 2024-03-09 19:44:51 浏览: 24
可以使用以下的VBA代码来复制多个Excel文件中的Sheet1:
```
Sub CopySheet1()
Dim wb As Workbook
Dim ws As Worksheet
Dim newWB As Workbook
Dim i As Integer
Dim folderPath As String
Dim fileName As String
'设置文件夹路径
folderPath = "C:\Users\username\Documents\Test\"
'循环遍历文件夹中的所有Excel文件
fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
'打开Excel文件
Set wb = Workbooks.Open(folderPath & fileName)
'复制Sheet1
Set ws = wb.Sheets("Sheet1")
ws.Copy
'创建新的Excel文件并保存
Set newWB = ActiveWorkbook
newWB.SaveAs folderPath & "Copy of " & fileName
newWB.Close
'关闭原始Excel文件
wb.Close
'获取下一个Excel文件
fileName = Dir
Loop
End Sub
```
在执行该代码之前,请确保将文件夹路径更改为您实际的文件夹路径,并且在所有Excel文件中都存在名为Sheet1的工作表。