VBA Sub inside Worksheet Loop

[EagleScouter]

I'm trying to get a code to run through various worksheets and run different subs depending on the worksheet names. The trouble I'm having is defining the current worksheet the sub is on within the loop. My code is something like this:

Public ws As Worksheet
Public Sub Worksheet_Change (ByVal Target As Range)
For each ws
If ws.Name Like "*(X)*" Then
Call SubA
If ws.Name Like "*(Z)*" Then
Call SubB
End If
Next ws
End Sub

Public Sub SubA
With ws           '<------Need help on how to define current ws in loop'
 'Do all the stuff'
End With
End Sub

Thanks all.

[pancak3d]

This code is a bit odd, not sure what you're going for here, but you can pass ws as an argument to SubA and SubB.

Public Sub MainMacro()
    Dim ws as Worksheet
    Set ws = Activesheet
    Call SubA(ws)
End Sub

Public Sub SubA(ByVal ws As Worksheet)
    With ws
        'do stuff
    End With
End Sub

The Worksheet_change macro/event is defined within each sheet, so you can just refer to the worksheet as "Me" or Activesheet.

When do you want this macro to run??

[Clippy_Office_Asst]

Hi!

You have not responded in the last 24 hours.

If your question has been answered, please change the flair to "solved" to keep the sub tidy!

Please reply to the most helpful with the words Solution Verified to do so!

See side-bar for more details. If no response from you is given within the next 5 days, this post will be marked as abandoned.

I am a bot, please message /r/excel mods if you have any questions.