VBA宏操作指定工作表数据（非活动工作表）：多格式表批量筛选需求

Got it, let's build a robust solution that handles both your consistent and inconsistent worksheets. The key here is to target columns by their header names instead of fixed positions, so the macro adapts to each sheet's structure automatically.

Step-by-Step Explanation & Code #

First, here's the complete macro. I've added comments to make it easy to tweak for your specific needs:

Sub ApplyStatusFilterToAllSheets()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim headerRow As Range
    Dim targetHeader As Range
    Dim statusHeaders As Object ' Dictionary to store all possible status column names
    
    ' Initialize dictionary: add ALL possible status-related headers from your 10 sheets
    Set statusHeaders = CreateObject("Scripting.Dictionary")
    statusHeaders.Add "Status", vbNullString ' For the 6 consistent sheets
    statusHeaders.Add "设备状态", vbNullString ' Example for one of the 4 inconsistent sheets
    statusHeaders.Add "运行状态", vbNullString ' Add more as needed for your 4 sheets
    statusHeaders.Add "系统状态", vbNullString ' Customize these to match your actual headers
    
    ' Loop through every worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next ' Skip errors if sheet is empty
        ' Get the used data range of the sheet
        Set dataRange = ws.UsedRange
        On Error GoTo 0
        
        If Not dataRange Is Nothing And dataRange.Rows.Count > 1 Then
            ' Assume header is in row 1 (adjust if your headers start at a different row)
            Set headerRow = dataRange.Rows(1)
            
            ' Check each target header to see if it exists in the current sheet
            For Each key In statusHeaders.Keys
                Set targetHeader = headerRow.Find(What:=key, LookIn:=xlValues, LookAt:=xlWhole)
                
                If Not targetHeader Is Nothing Then
                    ' Turn off existing filters first to avoid conflicts
                    If ws.AutoFilterMode Then ws.AutoFilterMode = False
                    
                    ' Apply filter to the found column
                    dataRange.AutoFilter Field:=targetHeader.Column, _
                                        Criteria1:="Active" ' Change this to your desired filter value
                    
                    ' Optional: Notify which sheet was processed
                    Debug.Print "Applied filter to sheet: " & ws.Name & " (Column: " & key & ")"
                    Exit For ' Move to next sheet once we find the correct header
                End If
            Next key
        End If
        
        ' Reset ranges for next sheet
        Set dataRange = Nothing
        Set headerRow = Nothing
        Set targetHeader = Nothing
    Next ws
    
    MsgBox "Filter application complete!", vbInformation
End Sub

Key Customization Points #

• Update the statusHeaders dictionary: Add all the different status-related column names from your 4 inconsistent sheets here. For example, if one sheet uses "Device Status" instead of "Status", add that to the dictionary.
• Adjust the header row: If your headers don't start in row 1, change dataRange.Rows(1) to the correct row number (e.g., dataRange.Rows(2)).
• Modify the filter criteria: Change Criteria1:="Active" to whatever value you need to filter for (e.g., "正常", "Running", etc.).

How It Works #

1. Dictionary for Header Flexibility: The dictionary lets us check all possible status column names in each sheet, so we don't have to hardcode positions.
2. Error Handling: Skips empty sheets or sheets without valid data to prevent runtime errors.
3. Clean Filter Reset: Turns off existing filters before applying new ones to avoid conflicts.
4. Automatic Column Detection: Uses Find to locate the status column regardless of its position in the sheet.

Notes #

• Make sure the Scripting Dictionary is enabled: This should work by default in most Excel versions, but if you get an error, go to Tools > References in the VBA editor and check "Microsoft Scripting Runtime".
• Test on a copy of your workbook first: Always back up your data before running macros!

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