工作簿切换问题求助：请调试VBA代码并提供解决方案

Let's break down the issues in your code first, then walk through a corrected version that does what I think you're aiming for—copying the "sheet1" from each workbook (sheet2.xlsx to sheet10.xlsx) into your main workbook as new sheets.

Key Issues in Your Original Code #

• Loop variable reset mistake: You set i = 2 inside the For x loop, so sheet_name always resolves to sheet2.xlsx every time the loop runs. That means you're opening the same file 9 times instead of iterating through sheet2 to sheet10.
• Unnecessary activation/selection: VBA doesn't require you to activate sheets or select cells to copy data. Doing so is slow and prone to errors (like if the user clicks away while the macro runs).
• Hardcoded target workbook: You reference Workbooks("C:\Users\me\Desktop\folder\sheet1.xls") directly—if this file isn't open, the macro will throw an error. You already declared wb_main as your main workbook, so use that instead.
• Unclosed workbooks: You open each source workbook but never close it. By the end of the loop, you'll have 9 extra Excel windows running in the background.
• Inconsistent variable naming: You declared source_sheet As Worksheet but used sourceSheet later. While VBA is case-insensitive, this makes your code harder to read and debug.

Corrected Code #

Here's a revised version that fixes all these issues and follows best practices for VBA:

Sub copy_spreadsheets()
    Dim wb As Workbook, wb_main As Workbook
    Dim path As String, sheet_name As String
    Dim x As Integer
    Dim sourceSheet As Worksheet, newSheet As Worksheet
    
    ' Set base path and reference the workbook running this macro
    path = "C:\Users\me\Desktop\folder\"
    Set wb_main = ThisWorkbook
    
    ' Loop through workbooks sheet2.xlsx to sheet10.xlsx
    For x = 2 To 10
        sheet_name = "sheet" & x & ".xlsx" ' Use loop counter x to get the right file
        
        ' Open the source workbook
        Set wb = Workbooks.Open(path & sheet_name)
        ' Reference the source sheet directly (no activation needed)
        Set sourceSheet = wb.Worksheets("sheet1")
        
        ' Copy the entire sheet to the end of your main workbook
        sourceSheet.Copy After:=wb_main.Sheets(wb_main.Sheets.Count)
        ' Rename the new sheet for clarity
        Set newSheet = wb_main.Sheets(wb_main.Sheets.Count)
        newSheet.Name = "From_Sheet" & x ' Adjust name as needed
        
        ' Close the source workbook without saving changes
        wb.Close SaveChanges:=False
    Next x
End Sub

What This Does Differently #

• Uses the loop counter x directly to generate each workbook name, so you correctly iterate through sheet2 to sheet10.
• Eliminates all Activate/Select calls by working directly with worksheet objects—this makes the macro faster and more reliable.
• Automatically closes each source workbook after copying, keeping your workspace clean.
• Renames the copied sheets to make it easy to track which data came from which file.

If You Only Need to Copy Cell Data (Not the Whole Sheet) #

If you don't want to copy the entire worksheet (just the used range of cells), replace the sourceSheet.Copy section with this:

' Add a new blank sheet to your main workbook
Set newSheet = wb_main.Sheets.Add(After:=wb_main.Sheets(wb_main.Sheets.Count))
newSheet.Name = "Data_From_Sheet" & x

' Copy only the used cells from the source sheet to the new sheet
sourceSheet.UsedRange.Copy Destination:=newSheet.Range("A1")

This copies values, formats, and formulas from the used area of the source sheet, without bringing over extra empty rows/columns.

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