Active sheet

[Guest]

Hi.

I have 1 workbook with 13 different worksheets. My question is:

I have a lot of vb code that is the same for each worksheet. ex:

When woorksheet(2) is chosen i want that to be the active worksheet, and
insead of this code:

MaterialRow = Worksheets(2).Range("Material").Row

I would love to have a code instead of Worksheets(2), if i have it like this
i have to change to worksheet(3) in the formula when worksheet(3) is chosen
etc etc.

And a code change the active worksheet.

Please help!!

 

[Bob Phillips]

Have you tried

MaterialRow = Activesheet.Range("Material").Row

 

[Guest]

Well Bob.

I want the sheet(2) to be active if that is chosen, something like this.

Dim wks as worksheet ' to tell that wks is the open sheet no mather the name

wks=worksheet(2) 'In sheet(2) when its active, later i can just change
that code to wks=worksheet(3) etc, if i dont do that i have to change 100 of
lines in each worksheet

Then i can change the formula:

MaterialRow = Worksheets(2).Range("Material").Row

to

MaterialRow = wks.Range("Material").Row

Then the formula should work for all the sheets when the sheet is active. Is
it possible?

 

[Guest]

Thanks Bob.

i didnt understand at first, know i checked and your formula works perfect.

Thanks again

 

[Bob Phillips]

Phew, thank goodness. I just read your follow-up and was thinking that my
previous answer still applied <G>

Bob

 

[Guest]

Thank you again
--
Nil Satis Nisi Optimum

Phew, thank goodness. I just read your follow-up and was thinking that my
previous answer still applied <G>

Bob