|
2 - 专项附加扣除信息电子模版(汇集).xls运行“合并当前目录下所有工作簿的全部工作表”宏时,提示“定义名称××”,需要按回车键或Y键确认,copy方法哪个参数可屏蔽该提示。
- Sub 合并当前目录下所有工作簿的全部工作表()
- Dim MyPath, MyName, AWbName
- Dim Wb As Workbook, WbN As String
- Dim G As Long
- Dim Num As Long
- Dim BOX As String
- Application.ScreenUpdating = False
- MyPath = ActiveWorkbook.Path
- MyName = Dir(MyPath & "" & "*.xls*")
- AWbName = ActiveWorkbook.Name
- ActiveWorkbook.Sheets(1).Activate
- With Workbooks(1).ActiveSheet
- .UsedRange.Clear
- Num = 0
- Do While MyName <> ""
- If MyName <> AWbName Then
- Set Wb = Workbooks.Open(MyPath & "" & MyName)
- Num = Num + 1
- .Cells(.Range("A65536:Z65536").End(xlUp).Row + IIf(Num > 1, 1, 0), 1) = Left(MyName, Len(MyName) - 4)
- For G = 2 To Sheets.Count - 1
- Wb.Sheets(G).Range("1:16").Copy .Cells(.Range("A65536:Z65536").End(xlUp).Row + 2, 1)
- Next
- WbN = WbN & Chr(13) & Wb.Name
- Wb.Close False
- End If
- MyName = Dir
- Loop
- Range("B1").Select
- End With
- Application.ScreenUpdating = True
- 'MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示"
- End Sub
复制代码
|
|