Home IF THEN ELSE condition based on sheet name
Reply: 3

IF THEN ELSE condition based on sheet name

Stefan Vetrila
1#
Stefan Vetrila Published in 2018-01-12 14:37:49Z

need some help with my VBA code.

I'm trying to execute two different codes depending on a sheet name: If Sheet name is A, execute "this code" Else execute "other code". The problem is that even if the sheet name is not A, the VBA still executes "this code" when it should execute the "other code". Thank you all in advance!

Here is the VBA code:

Sub Macro2()

    Sheets(2).Activate
[a7] = ActiveSheet.Name
    ' Most frequent used City&Country

If Sheets(2).Range("a7") Like "Sender*" Then
    Sheets("Send Pivots").Visible = True
    Sheets("Send Pivots").Select
    Range("D4").Select
    ActiveSheet.PivotTables("SndAddPvt").PivotFields("Count of Sender Address"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("SndAddPvt").AddDataField ActiveSheet.PivotTables( _
        "SndAddPvt").PivotFields("Send Consumer City"), "Count of Send Consumer City", _
        xlCount
    With ActiveSheet.PivotTables("SndAddPvt").PivotFields("Send Consumer City")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("SndAddPvt").PivotFields("Sender Address").Orientation _
        = xlHidden
    Range("E4").Select
    ActiveSheet.PivotTables("SndAddPvt").PivotFields("Send Consumer City"). _
        AutoSort xlDescending, "Count of Send Consumer City", ActiveSheet.PivotTables( _
        "SndAddPvt").PivotColumnAxis.PivotLines(1), 1
    With ActiveSheet.PivotTables("SndAddPvt").PivotFields("Send Consumer Country")
        .Orientation = xlRowField
        .Position = 2
    End With
      Range("S4").Select
    With ActiveSheet.PivotTables("SndGIDPvt").PivotFields( _
        "Send Consumer ID Type Photo")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("SndGIDPvt").PivotFields( _
        "Send Consumer ID Issue Country")
        .Orientation = xlRowField
        .Position = 3
    End With
    Sheets("Send Pivots").Visible = False
Else
    Sheets("Receives Pivots").Visible = True
        Sheets("Receives Pivots").Select
    Range("D4").Select
    ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Count of Receiver Address"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("RcvAddPvt").AddDataField ActiveSheet.PivotTables( _
        "RcvAddPvt").PivotFields("Receive Consumer City"), "Count of Receive Consumer City", _
        xlCount
    With ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Receive Consumer City")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Receiver Address").Orientation _
        = xlHidden
    Range("E4").Select
    ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Receive Consumer City"). _
        AutoSort xlDescending, "Count of Receive Consumer City", ActiveSheet.PivotTables( _
        "RcvAddPvt").PivotColumnAxis.PivotLines(1), 1
    With ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Receive Consumer Country")
        .Orientation = xlRowField
        .Position = 2
    End With
      Range("S4").Select
    With ActiveSheet.PivotTables("RcvGIDPvt").PivotFields( _
        "Receive Consumer ID Type Photo")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("RcvGIDPvt").PivotFields( _
        "Receive Consumer ID Issue Country")
        .Orientation = xlRowField
        .Position = 3
    End With
    Sheets("Receives Pivots").Visible = False
End If

End Sub
K.Davis
2#
K.Davis Reply to 2018-01-12 14:48:41Z

That's because you are checking if Sheets(2) like "Sender*", not the active sheet. This may always be true if Sheets(2) is in fact like sender.

I made some adjustments.

Sub Macro2()

If ActiveSheet.Name Like "Sender*" Then
    Sheets("Send Pivots").Visible = True
    Sheets("Send Pivots").Select
    Range("D4").Select
    ActiveSheet.PivotTables("SndAddPvt").PivotFields("Count of Sender Address"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("SndAddPvt").AddDataField ActiveSheet.PivotTables( _
        "SndAddPvt").PivotFields("Send Consumer City"), "Count of Send Consumer City", _
        xlCount
    With ActiveSheet.PivotTables("SndAddPvt").PivotFields("Send Consumer City")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("SndAddPvt").PivotFields("Sender Address").Orientation _
        = xlHidden
    Range("E4").Select
    ActiveSheet.PivotTables("SndAddPvt").PivotFields("Send Consumer City"). _
        AutoSort xlDescending, "Count of Send Consumer City", ActiveSheet.PivotTables( _
        "SndAddPvt").PivotColumnAxis.PivotLines(1), 1
    With ActiveSheet.PivotTables("SndAddPvt").PivotFields("Send Consumer Country")
        .Orientation = xlRowField
        .Position = 2
    End With
      Range("S4").Select
    With ActiveSheet.PivotTables("SndGIDPvt").PivotFields( _
        "Send Consumer ID Type Photo")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("SndGIDPvt").PivotFields( _
        "Send Consumer ID Issue Country")
        .Orientation = xlRowField
        .Position = 3
    End With
    Sheets("Send Pivots").Visible = False
Else
    Sheets("Receives Pivots").Visible = True
        Sheets("Receives Pivots").Select
    Range("D4").Select
    ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Count of Receiver Address"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("RcvAddPvt").AddDataField ActiveSheet.PivotTables( _
        "RcvAddPvt").PivotFields("Receive Consumer City"), "Count of Receive Consumer City", _
        xlCount
    With ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Receive Consumer City")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Receiver Address").Orientation _
        = xlHidden
    Range("E4").Select
    ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Receive Consumer City"). _
        AutoSort xlDescending, "Count of Receive Consumer City", ActiveSheet.PivotTables( _
        "RcvAddPvt").PivotColumnAxis.PivotLines(1), 1
    With ActiveSheet.PivotTables("RcvAddPvt").PivotFields("Receive Consumer Country")
        .Orientation = xlRowField
        .Position = 2
    End With
      Range("S4").Select
    With ActiveSheet.PivotTables("RcvGIDPvt").PivotFields( _
        "Receive Consumer ID Type Photo")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("RcvGIDPvt").PivotFields( _
        "Receive Consumer ID Issue Country")
        .Orientation = xlRowField
        .Position = 3
    End With
    Sheets("Receives Pivots").Visible = False
End If

End Sub
Foxfire And Burns And Burns
3#
Foxfire And Burns And Burns Reply to 2018-01-12 14:53:04Z

I tried with this and worked for me

Dim ws As Worksheet
Set ws = ActiveSheet

If InStr(1, ws.Name, "Sender", vbTextCompare) > 0 Then
    'execute this code because activesheet is Like Sender*
Else
    'execute another code
End If

Set ws = Nothing
Stefan Vetrila
4#
Stefan Vetrila Reply to 2018-01-12 18:54:03Z

here is what i did:

Sub Macro2()  

Dim ws As Worksheet
Set ws = Sheets(2)

ws.Activate
If InStr(1, ws.Name, "Sender", vbTextCompare) > 0 Then
    Sheets("Send Pivots").Visible = True
    Sheets("Send Pivots").Select
    Range("A1") = "first option"
Else
    Sheets("Receives Pivots").Visible = True
    Sheets("Receives Pivots").Select
    Range("A1") = "Second option"
End If

End Sub

Now the 2nd sheet name is "Receiver Summary" so the code should unhide the sheet "Receives Pivot". When the code is executed, it unhides the sheet "Send pivots"

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304392 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO