VBA遍历文件夹Excel文件,复制指定工作表到MASTER工作簿并重命名
修改后的完整代码
Sub Assets2Master() Dim xRg As Range Dim xSelItem As Variant Dim xFileDlg As FileDialog Dim xFileName, xSheetName, xRgStr, xNewSheetName As String Dim xBook, xWorkBook As Workbook Dim xNewSheet As Worksheet On Error Resume Next Application.DisplayAlerts = False Application.EnableEvents = False Application.ScreenUpdating = False xSheetName = "Annual" xRgStr = "B1:GI100" Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker) With xFileDlg If .Show = -1 Then xSelItem = .SelectedItems.Item(1) Set xWorkBook = ThisWorkbook ' 若需要保留原汇总用的MASTER工作表,可把对应代码加回此处 xFileName = Dir(xSelItem & "\*.xlsx", vbNormal) If xFileName = "" Then Exit Sub Do Until xFileName = "" Set xBook = Workbooks.Open(xSelItem & "\" & xFileName) ' 生成新工作表名:去掉原文件后缀后拼接 - Annual xNewSheetName = Replace(xFileName, ".xlsx", "") & " - Annual" ' 在总工作簿末尾新建工作表 Set xNewSheet = xWorkBook.Sheets.Add(after:=xWorkBook.Worksheets(xWorkBook.Worksheets.Count)) xNewSheet.Name = xNewSheetName ' 复制内容到新工作表A1起始位置 Set xRg = xBook.Worksheets(xSheetName).Range(xRgStr) xRg.Copy xNewSheet.Range("A1") ' 调整当前新工作表格式 Set xRg = xNewSheet.UsedRange xRg.ClearFormats xRg.UseStandardHeight = True xRg.UseStandardWidth = True xFileName = Dir() xBook.Close Loop End If End With Application.DisplayAlerts = True Application.EnableEvents = True Application.ScreenUpdating = True End Sub
核心调整说明
- 移除了原有所有内容追加到同一张MASTER工作表的逻辑,若需要保留汇总总表可自行恢复对应代码
- 遍历每个文件时新增了工作表命名逻辑,自动提取原文件名去掉后缀后拼接
- Annual作为新表名 - 每个文件对应新建一张独立工作表,复制的内容直接粘贴到新表的A1单元格
- 修正了原代码中
et xRg的笔误,将格式调整的作用范围修改为每个新生成的工作表 - 现有逻辑遇到重名文件时会自动跳过命名冲突报错,若需要处理重名场景可自行添加同名后缀加序号的逻辑
内容的提问来源于stack exchange,提问作者Afikasky
相关产品推荐
相关产品推荐

