VBA代码异常求助：按工作表名称分支执行逻辑错误

Hey there, let's dig into why your code is running the wrong branch even when the sheet name doesn't match "Sender*".

The Root Cause #

Your current code writes the sheet name to cell A7 first, then checks that cell's value for the "Sender*" pattern. This introduces two critical points of failure:

1. If you've run the code before, cell A7 might still hold an old "Sender..." value from a previous execution, skewing your check.
2. While you do activate Sheets(2) first, relying on cell values for this kind of logic is unnecessary and fragile—you can directly check the sheet's Name property instead, which is far more reliable.

The Fix #

First, replace your roundabout cell-based check with a direct comparison of the sheet's name. Then, we can clean up the code to avoid overusing Activate and Select (these are common sources of VBA bugs, as they depend on the active state of your workbook).

Here's the revised code:

Sub Macro2()
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Sheets(2)
    
    ' Directly check the sheet's name instead of using a cell
    If targetSheet.Name Like "Sender*" Then
        With ThisWorkbook.Sheets("Send Pivots")
            .Visible = True
            ' Work with the pivot table directly without selecting
            With .PivotTables("SndAddPvt")
                .PivotFields("Count of Sender Address").Orientation = xlHidden
                .AddDataField .PivotFields("Send Consumer City"), "Count of Send Consumer City", xlCount
                With .PivotFields("Send Consumer City")
                    .Orientation = xlRowField
                    .Position = 2
                End With
                .PivotFields("Sender Address").Orientation = xlHidden
                .PivotFields("Send Consumer City").AutoSort xlDescending, "Count of Send Consumer City", .PivotColumnAxis.PivotLines(1), 1
                With .PivotFields("Send Consumer Country")
                    .Orientation = xlRowField
                    .Position = 2
                End With
            End With
            With .PivotTables("SndGIDPvt")
                With .PivotFields("Send Consumer ID Type Photo")
                    .Orientation = xlRowField
                    .Position = 2
                End With
                With .PivotFields("Send Consumer ID Issue Country")
                    .Orientation = xlRowField
                    .Position = 3
                End With
            End With
            .Visible = False
        End With
    Else
        With ThisWorkbook.Sheets("Receives Pivots")
            .Visible = True
            With .PivotTables("RcvAddPvt")
                .PivotFields("Count of Receiver Address").Orientation = xlHidden
                .AddDataField .PivotFields("Receive Consumer City"), "Count of Receive Consumer City", xlCount
                With .PivotFields("Receive Consumer City")
                    .Orientation = xlRowField
                    .Position = 2
                End With
                .PivotFields("Receiver Address").Orientation = xlHidden
                .PivotFields("Receive Consumer City").AutoSort xlDescending, "Count of Receive Consumer City", .PivotColumnAxis.PivotLines(1), 1
                With .PivotFields("Receive Consumer Country")
                    .Orientation = xlRowField
                    .Position = 2
                End With
            End With
            With .PivotTables("RcvGIDPvt")
                With .PivotFields("Receive Consumer ID Type Photo")
                    .Orientation = xlRowField
                    .Position = 2
                End With
                With .PivotFields("Receive Consumer ID Issue Country")
                    .Orientation = xlRowField
                    .Position = 3
                End With
            End With
            .Visible = False
        End With
    End If
End Sub

Key Improvements #

• Direct Name Check: We now use targetSheet.Name Like "Sender*" which eliminates any dependency on cell values.
• Removed Activate/Select: By using With blocks to reference sheets and pivot tables directly, the code is faster, more reliable, and less prone to state-based bugs.
• Cleaner Structure: Nested With blocks make the code easier to read and maintain.

This should resolve the issue where the wrong branch was executing—now the code will only run the "Sender" logic if Sheets(2) actually has a name starting with "Sender".

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