call code for all sheets

[rwnelson]

Through browsing this group, I have been able to apply code to all the
sheets in my workbook with the following test code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$E$2" Then Exit Sub

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
sh.Range("G2") = "test worked"
Next

End Sub
'--------------------------------------------------------------------------------------------

Is there any way to call a sub to all worksheets. I've tried
replaceing the SH.RANGE line with CALL TEST and it did not apply it to
all sheets. I've tried using WITH statements as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$E$2" Then Exit Sub

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
with sh
call test
end with
Next

End Sub

This did not work either. Any suggestions?

[Jim Thomlinson]

sh is your worksheet object, but call test needs to know which sheet. Try this

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
with sh
call test(sh)
end with
Next sh

Sub Test(sh as worksheet)
msgbox sh.name
end sub
--
HTH...

Jim Thomlinson


"rwnelson" wrote:

> Through browsing this group, I have been able to apply code to all the
> sheets in my workbook with the following test code.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Target.Address <> "$E$2" Then Exit Sub
>
> Dim sh As Worksheet
> For Each sh In ActiveWorkbook.Worksheets
> sh.Activate
> sh.Range("G2") = "test worked"
> Next
>
> End Sub
> '--------------------------------------------------------------------------------------------
>
> Is there any way to call a sub to all worksheets. I've tried
> replaceing the SH.RANGE line with CALL TEST and it did not apply it to
> all sheets. I've tried using WITH statements as follows:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Target.Address <> "$E$2" Then Exit Sub
>
> Dim sh As Worksheet
> For Each sh In ActiveWorkbook.Worksheets
> sh.Activate
> with sh
> call test
> end with
> Next
>
> End Sub
>
> This did not work either. Any suggestions?
>
>

[rwnelson]

Thank you for your response. I put in the code and got the message box
on each sheet that stated the sheet name but when I tried to put in
something like range("G2") = "test worked" - only sheet 1 received the
changes, sheets 2 and 3 did not. Am I missing something? I tried the
two codes below.

Sub Test(sh As Worksheet)
MsgBox sh.Name
Range("G2") = "test worked"
End Sub
'__________________________

Sub Test(sh As Worksheet)
Range("G2") = "test worked"
End Sub

[dok112]

You need to tell it to run it on each sheet. your current macro is only set for the active sheet.

Yours:
Sub Test(sh As Worksheet)
Range("G2") = "test worked"
End Sub

Correct:
Sub test()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range("G2") = "test worked"
Next
End Sub

Thank you for your response. I put in the code and got the message box
on each sheet that stated the sheet name but when I tried to put in
something like range("G2") = "test worked" - only sheet 1 received the
changes, sheets 2 and 3 did not. Am I missing something? I tried the
two codes below.

Sub Test(sh As Worksheet)
MsgBox sh.Name
Range("G2") = "test worked"
End Sub
'__________________________

Sub Test(sh As Worksheet)
Range("G2") = "test worked"
End Sub

[Jim Thomlinson]

Unless otherwise specified code always executes on the active sheet. That
being the case, the only thing you are missing is a reference to sh. Try
this...

Sub Test(sh As Worksheet)
sh.Range("G2") = "test worked"
End Sub

--
HTH...

Jim Thomlinson


"rwnelson" wrote:

> Thank you for your response. I put in the code and got the message box
> on each sheet that stated the sheet name but when I tried to put in
> something like range("G2") = "test worked" - only sheet 1 received the
> changes, sheets 2 and 3 did not. Am I missing something? I tried the
> two codes below.
>
> Sub Test(sh As Worksheet)
> MsgBox sh.Name
> Range("G2") = "test worked"
> End Sub
> '__________________________
>
> Sub Test(sh As Worksheet)
> Range("G2") = "test worked"
> End Sub
>
>

[rwnelson]

Thank you all. Worked perfectly.