Excel VBA跨工作簿复制粘贴代码故障求助

Let's walk through the issues in your current code and get it working correctly to copy data from matching worksheet names between the two workbooks.

Key Problems in the Original Code #

• Invalid Worksheet Assignment: The line Set sh = Segmented General Ledger Trial is syntactically incorrect — you can't reference a worksheet like that without quotes or proper workbook context. This is the source of your initial sheet name error.
• Incorrect Loop Range: For i = 20 To aw.Sheets.Count will only loop through worksheets starting from the 20th one in your target workbook. If you need to update all 20 sheets, this range is wrong.
• Broken Type Check: TypeName(sh) <> "Segmented General Ledger Trial" is checking the object type (which will always be Worksheet, not the sheet name), so your copy logic never runs.
• Misplaced Error Handling: The error resume next isn't properly scoped, which could hide other issues in your code.

Corrected VBA Code #

Sub CopyWorkbook()
    Dim sourceWB As Workbook
    Dim targetWB As Workbook
    Dim sourceWS As Worksheet
    Dim targetWS As Worksheet
    Dim wsName As String
    
    ' Set reference to your target workbook (the one with 20 sheets)
    Set targetWB = Application.ActiveWorkbook
    
    ' Open the source workbook
    Set sourceWB = Application.Workbooks.Open("S:\Proefbalanse\PastelTB\Segmented General Ledger Trial.XLS")
    
    ' Loop through each worksheet in the target workbook
    For Each targetWS In targetWB.Worksheets
        wsName = targetWS.Name
        
        ' Try to find the matching worksheet in the source workbook
        On Error Resume Next
        Set sourceWS = sourceWB.Worksheets(wsName)
        On Error GoTo 0 ' Turn off error handling after this check
        
        ' If a matching sheet exists, copy the data
        If Not sourceWS Is Nothing Then
            ' Clear existing data first to ensure full overwrite
            targetWS.Range("A:F").ClearContents
            ' Copy data from source to target, starting at A1
            sourceWS.Range("A:F").Copy Destination:=targetWS.Range("A1")
            ' Reset the sourceWS variable for the next iteration
            Set sourceWS = Nothing
        End If
    Next targetWS
    
    ' Clean up
    Application.CutCopyMode = False
    sourceWB.Close SaveChanges:=False ' Don't save changes to the source file
End Sub

What This Code Does #

• Clear Overwrites: It first clears existing data in columns A:F of the target sheet to ensure you're fully overwriting old content.
• Safe Matching: Uses On Error Resume Next only to check for the existence of a matching worksheet name in the source workbook, then disables error handling right after to avoid masking other issues.
• Full Workbook Loop: Iterates through every worksheet in your target workbook (all 20) and only copies data if a sheet with the exact same name exists in the source workbook.
• Clean Cleanup: Properly closes the source workbook without saving changes (adjust SaveChanges:=True if you need to save edits to the source file, though that's unlikely here).

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