如何修改VBA代码将指定Excel工作表合并至当前工作簿？

Got it, let's adjust your existing VBA code to only copy the exact worksheets you need: Sheet A from Workbook 1 and Sheet C from Workbook 2. Here's the revised code and breakdown:

Revised VBA Code #

Sub MergeSpecificWorksheets()
 Dim Path As String
 Dim FileName As String
 Dim ws As Worksheet
 Dim wb As Workbook
 Application.EnableEvents = False
 Application.ScreenUpdating = False
 Path = "C:\Users\Name\Documents\Data\"
 FileName = Dir(Path & "*.xls", vbNormal) ' Removed extra backslash to avoid invalid path
 Do Until FileName = ""
 Set wb = Workbooks.Open(FileName:=Path & FileName)
 For Each ws In wb.Worksheets
 ' Only copy sheets named "A" or "C"
 If ws.Name = "A" Or ws.Name = "C" Then
 ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
 End If
 Next ws
 wb.Close False
 FileName = Dir()
 Loop
 Application.EnableEvents = True
 Application.ScreenUpdating = True
End Sub

Key Changes Explained #

• Added Worksheet Filter: We added an If condition to check each worksheet's name—only sheets matching "A" or "C" get copied over. This automatically skips Sheet B from Workbook 2.
• Fixed Path Syntax: Removed the extra backslash in Dir(Path & "\*.xls") since your Path variable already ends with a backslash. This prevents invalid file paths like C:\Users\Name\Documents\Data\\*.xls.
• Kept Performance Tweaks: Retained the Application.EnableEvents and Application.ScreenUpdating toggles to keep the macro fast and avoid screen flicker during execution.

Quick Note #

If your target workbook already has a sheet named "A" or "C", Excel will automatically append a number in parentheses (e.g., "A(1)") to the copied sheet to avoid naming conflicts.

内容的提问来源于stack exchange，提问作者Gun hanwei