i have written a sub test_values in modules named Identifychages. I know that
by runing the module it will perform the sub on the excel worksheet that i am
opening. however, i wish to run this sub on mulitiple worksheets on the same
workbook.how can i do it?anyone can give me advice? i still very new to vba
coding in excel.

Violet, you want to pass the worksheet(s) as a parameter into your sub.
Then you can call the sub from another routine and pass in the worksheets.
Here's an example:


public sub test_values_Master( )
dim wkb as workbook
dim wks as worksheet

set wkb = activeworkbook ' or set wkb equal to any workbook you want

for each wks in wkb.worksheets
call test_values( wks )
next wks
end sub


public sub test_values( wks as worksheet )
' perform operations on wks
end sub

Hope this helps - let me know if it wasn't what you were looking for.

Regards,
Bill


"violet" wrote:

i have written a sub test_values in modules named Identifychages. I know that
by runing the module it will perform the sub on the excel worksheet that i am
opening. however, i wish to run this sub on mulitiple worksheets on the same
workbook.how can i do it?anyone can give me advice? i still very new to vba
coding in excel.

Bill,

The code you have for me is for all the worksheets in the workbook?what if i
wan only selected few?

"Bill Pfister" wrote:


Violet, you want to pass the worksheet(s) as a parameter into your sub.
Then you can call the sub from another routine and pass in the worksheets.
Here's an example:


public sub test_values_Master( )
dim wkb as workbook
dim wks as worksheet

set wkb = activeworkbook ' or set wkb equal to any workbook you want

for each wks in wkb.worksheets
call test_values( wks )
next wks
end sub


public sub test_values( wks as worksheet )
' perform operations on wks
end sub

Hope this helps - let me know if it wasn't what you were looking for.

Regards,
Bill


"violet" wrote:

i have written a sub test_values in modules named Identifychages. I know that
by runing the module it will perform the sub on the excel worksheet that i am
opening. however, i wish to run this sub on mulitiple worksheets on the same
workbook.how can i do it?anyone can give me advice? i still very new to vba
coding in excel.

Public Sub test_values_Master()
Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = ActiveWorkbook ' or set wkb equal to any workbook you want

For Each wks In wkb.Worksheets(Array("Sheet2", "Sheet3"))
Call test_values(wks)
Next wks
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"violet" wrote in message
...
Bill,

The code you have for me is for all the worksheets in the workbook?what if
i
wan only selected few?

"Bill Pfister" wrote:


Violet, you want to pass the worksheet(s) as a parameter into your sub.
Then you can call the sub from another routine and pass in the
worksheets.
Here's an example:


public sub test_values_Master( )
dim wkb as workbook
dim wks as worksheet

set wkb = activeworkbook ' or set wkb equal to any workbook you want

for each wks in wkb.worksheets
call test_values( wks )
next wks
end sub


public sub test_values( wks as worksheet )
' perform operations on wks
end sub

Hope this helps - let me know if it wasn't what you were looking for.

Regards,
Bill


"violet" wrote:

i have written a sub test_values in modules named Identifychages. I
know that
by runing the module it will perform the sub on the excel worksheet
that i am
opening. however, i wish to run this sub on mulitiple worksheets on
the same
workbook.how can i do it?anyone can give me advice? i still very new
to vba
coding in excel.

strange..it doesn't work...

"Bob Phillips" wrote:

Public Sub test_values_Master()
Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = ActiveWorkbook ' or set wkb equal to any workbook you want

For Each wks In wkb.Worksheets(Array("Sheet2", "Sheet3"))
Call test_values(wks)
Next wks
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"violet" wrote in message
...
Bill,

The code you have for me is for all the worksheets in the workbook?what if
i
wan only selected few?

"Bill Pfister" wrote:


Violet, you want to pass the worksheet(s) as a parameter into your sub.
Then you can call the sub from another routine and pass in the
worksheets.
Here's an example:


public sub test_values_Master( )
dim wkb as workbook
dim wks as worksheet

set wkb = activeworkbook ' or set wkb equal to any workbook you want

for each wks in wkb.worksheets
call test_values( wks )
next wks
end sub


public sub test_values( wks as worksheet )
' perform operations on wks
end sub

Hope this helps - let me know if it wasn't what you were looking for.

Regards,
Bill


"violet" wrote:

i have written a sub test_values in modules named Identifychages. I
know that
by runing the module it will perform the sub on the excel worksheet
that i am
opening. however, i wish to run this sub on mulitiple worksheets on
the same
workbook.how can i do it?anyone can give me advice? i still very new
to vba
coding in excel.

You have to change the values in the array to your sheet names

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"violet" wrote in message
...
strange..it doesn't work...

"Bob Phillips" wrote:

Public Sub test_values_Master()
Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = ActiveWorkbook ' or set wkb equal to any workbook you want

For Each wks In wkb.Worksheets(Array("Sheet2", "Sheet3"))
Call test_values(wks)
Next wks
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"violet" wrote in message
...
Bill,

The code you have for me is for all the worksheets in the
workbook?what if
i
wan only selected few?

"Bill Pfister" wrote:


Violet, you want to pass the worksheet(s) as a parameter into your
sub.
Then you can call the sub from another routine and pass in the
worksheets.
Here's an example:


public sub test_values_Master( )
dim wkb as workbook
dim wks as worksheet

set wkb = activeworkbook ' or set wkb equal to any workbook you
want

for each wks in wkb.worksheets
call test_values( wks )
next wks
end sub


public sub test_values( wks as worksheet )
' perform operations on wks
end sub

Hope this helps - let me know if it wasn't what you were looking
for.

Regards,
Bill


"violet" wrote:

i have written a sub test_values in modules named Identifychages.
I
know that
by runing the module it will perform the sub on the excel
worksheet
that i am
opening. however, i wish to run this sub on mulitiple worksheets
on
the same
workbook.how can i do it?anyone can give me advice? i still very
new
to vba
coding in excel.

got another problem now...you c my code is for highlighting changes..now prob
is that when one of the sheet that i declare in the array is empty..the array
of worksheet seem like will not load as any sheet that is declare after the
blank sheet, the macro "changes" will not work on those sheets.

eg.

For Each wks In wkb.Worksheets(Array("Korea", "China", "Malaysia", "Brunei"))
call changes
next wks

if korea sheet has no data, "china" sheet and the rest will not execute
changes

"violet" wrote:

i have written a sub test_values in modules named Identifychages. I know that
by runing the module it will perform the sub on the excel worksheet that i am
opening. however, i wish to run this sub on mulitiple worksheets on the same
workbook.how can i do it?anyone can give me advice? i still very new to vba
coding in excel.

Can you show us the changes macro please Violet?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"violet" wrote in message
...
got another problem now...you c my code is for highlighting changes..now
prob
is that when one of the sheet that i declare in the array is empty..the
array
of worksheet seem like will not load as any sheet that is declare after
the
blank sheet, the macro "changes" will not work on those sheets.

eg.

For Each wks In wkb.Worksheets(Array("Korea", "China", "Malaysia",
"Brunei"))
call changes
next wks

if korea sheet has no data, "china" sheet and the rest will not execute
changes

"violet" wrote:

i have written a sub test_values in modules named Identifychages. I know
that
by runing the module it will perform the sub on the excel worksheet that
i am
opening. however, i wish to run this sub on mulitiple worksheets on the
same
workbook.how can i do it?anyone can give me advice? i still very new to
vba
coding in excel.

can you post the contents of the "changes" sub?


"violet" wrote:

got another problem now...you c my code is for highlighting changes..now prob
is that when one of the sheet that i declare in the array is empty..the array
of worksheet seem like will not load as any sheet that is declare after the
blank sheet, the macro "changes" will not work on those sheets.

eg.

For Each wks In wkb.Worksheets(Array("Korea", "China", "Malaysia", "Brunei"))
call changes
next wks

if korea sheet has no data, "china" sheet and the rest will not execute
changes

"violet" wrote:

i have written a sub test_values in modules named Identifychages. I know that
by runing the module it will perform the sub on the excel worksheet that i am
opening. however, i wish to run this sub on mulitiple worksheets on the same
workbook.how can i do it?anyone can give me advice? i still very new to vba
coding in excel.