遍历动态命名打开工作簿删除CC表不符行时VBA代码报错求助

Let's break down why you're hitting that error and fix it step by step.

What's Causing the Error? #

The main issues in your code are related to unqualified references (not specifying exactly which workbook/worksheet you're targeting) and unhandled edge cases:

• When you call Worksheets("CC") without linking it to the current wb in your loop, VBA defaults to the active workbook—which might not be the one you're iterating over. This leads to a "subscript out of range" error if the active workbook doesn't have a sheet named CC, or pulls data from the wrong sheet entirely.
• Rows(j).Delete also doesn't specify the worksheet, so it tries to delete rows from the active sheet instead of the CC sheet in your target workbook.
• Your lastRowy function could return an error or 0 if the sheet is empty, which would break the loop.

Corrected Code #

Here's the revised version of your code with all these issues fixed:

Sub filter()
    Dim wbs As Workbooks
    Dim wb As Workbook
    Dim wsCC As Worksheet
    Dim lastRow As Long
    Dim j As Long
    
    Set wbs = Application.Workbooks
    
    For Each wb In wbs
        ' First, check if the workbook has a "CC" worksheet to avoid errors
        On Error Resume Next
        Set wsCC = wb.Worksheets("CC")
        On Error GoTo 0
        
        If Not wsCC Is Nothing Then
            lastRow = lastRowy(wsCC) ' Pass the specific CC sheet to the function
            
            ' Only loop if there are rows to check (skip empty sheets)
            If lastRow > 0 Then
                ' Loop from last row up to 2 (assuming row 1 is a header; adjust to 1 if no header)
                For j = lastRow To 2 Step -1
                    ' Choose one of these comparison options:
                    ' 1. Include file extension (e.g., "MyWorkbook.xlsx")
                    ' If wb.Name <> wsCC.Cells(j, "D").Value Then
                    
                    ' 2. Exclude file extension (e.g., "MyWorkbook")
                    If Left(wb.Name, InStrRev(wb.Name, ".") - 1) <> wsCC.Cells(j, "D").Value Then
                        wsCC.Rows(j).Delete ' Specify the exact sheet to delete rows from
                    End If
                Next j
            End If
            
            Set wsCC = Nothing ' Reset the variable for the next workbook
        End If
    Next wb
End Sub

Function lastRowy(sh As Worksheet) As Long
    On Error Resume Next
    lastRowy = sh.Cells.Find(what:="*", _
                            After:=sh.Range("A1"), _
                            LookAt:=xlPart, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
    
    ' Safely return 0 if no data is found on the sheet
    If lastRowy = 0 Then lastRowy = 0
End Function

Key Changes Explained #

• Qualified References: We now explicitly set wsCC = wb.Worksheets("CC") and use wsCC for all operations on the CC sheet—this ensures we're always working with the sheet inside the current workbook in the loop, not the active one.
• Missing Sheet Check: Added a safety check to skip workbooks that don't have a "CC" worksheet, so your code won't crash if one is missing.
• Explicit Row Deletion: wsCC.Rows(j).Delete ensures we delete rows from the correct sheet, not whatever sheet happens to be active.
• Workbook Name Flexibility: Included two options for comparing the workbook name—with or without the file extension (like .xlsx). Pick the one that matches what's in your D column.
• Empty Sheet Handling: Updated the lastRowy function to safely return 0 if the sheet is empty, preventing the loop from running when there's nothing to process.

How to Use #

1. Replace your existing code with this version.
2. Adjust the comparison line to match whether your D column includes file extensions or not.
3. Run the filter subroutine—it will process all open workbooks with a "CC" sheet, deleting rows where column D doesn't match the workbook name.

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