|
本帖最后由 爱疯 于 2015-3-30 09:52 编辑
请问如何批量为同一个文件夹中的每一个工作簿添加一个带内容的相同名称的工作表??
- Sub Macro1()
- Dim arr, wb As Workbook, i&, n&
- Set wb = ThisWorkbook
- arr = Range("a1:d" & Range("a65536").End(xlUp).Row + 1)
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Application.SheetsInNewWorkbook = 2
- n = 2
- For i = 3 To UBound(arr)
- If arr(i, 1) <> arr(i - 1, 1) Then
- Set rng = Range(Cells(n, 1), Cells(i - 1, 4))
- With Workbooks.Add
- wb.Sheets(1).Rows(1).Copy [a1]
- rng.Copy [a2]
- wb.Sheets(2).UsedRange.Copy Sheets(2).[a1]
- Sheets(1).Name = arr(i - 1, 1)
- Sheets(2).Name = "总表"
- .SaveAs Filename:=ThisWorkbook.Path & "" & arr(i - 1, 1) & ".xls"
- .Close 0
- End With
- n = i
- End If
- Next
- MsgBox "OK"
- Application.SheetsInNewWorkbook = 3
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|