VBA Help. Select Case based on worksheet name

[bbalch]

I have three different macros that make various format adjustments. 1) intacct_CMA 2) intacct_events 3) Intacct

I'm trying to consolidate the three macros into one by using a Select Case Statement based on the Worksheet names.
I would like for:
- macro #1 to run on the worksheet named "Detailed P&L - Unaudited - CMA"
- macro #2 to run on P&L - General, P&L - Industry Initiatives, P&L - Christmas, P&L - Awards Show, and P&L - Music Fest
- macro #3 on all other sheets

The code below loops through the workbook but does not call the formatting macros based on the worksheet names.

Any suggestions on how to modify?

Dim ws As Worksheet    For Each ws In Worksheets
        Select Case UCase(ws.Name)
           Case "Detailed P&L - Unaudited - CMA "
                With ws
                    Call intacct_CMA
                End With
            Case "P&L - General", "P&L - Industry Initiatives", "P&L - Christmas", "P&L - Awards Show", "P&L - Music Fest"
                With ws
                    Call intacct_events
                End With
           Case Else
                With ws
                    Call Intacct
                End With
     End Select
    Next ws

 

[Michael M]

try adding the line in red

For Each ws In Worksheets
 [color=red]ws.Activate[/color]
    Select Case UCase(ws.Name)
           Case "Detailed P&L - Unaudited - CMA "
                With ws
                    Call intacct_CMA
                End With
            Case "P&L - General", "P&L - Industry Initiatives", "P&L - Christmas", "P&L - Awards Show", "P&L - Music Fest"
                With ws
                    Call intacct_events
                End With
           Case Else
                With ws
                    Call Intacct
                End With
     End Select

 

[Michael M]

You are also specifying Upper Case

UCase(ws.Name)

When your sheet names are not ...try removing that line !

 

[bbalch]

That did the trick! Thank you!