Active sheet
 

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!!
--
Nil Satis Nisi Optimum

Have you tried

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


--
HTH

Bob Phillips

"Michael" wrote in message
...
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!!
--
Nil Satis Nisi Optimum

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?



--
Nil Satis Nisi Optimum


"Bob Phillips" wrote:

Have you tried

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


--
HTH

Bob Phillips

"Michael" wrote in message
...
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!!
--
Nil Satis Nisi Optimum

Thanks Bob.

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

Thanks again :-)
--
Nil Satis Nisi Optimum


"Bob Phillips" wrote:

Have you tried

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


--
HTH

Bob Phillips

"Michael" wrote in message
...
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!!
--
Nil Satis Nisi Optimum

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

Bob

"Michael" wrote in message
...
Thanks Bob.

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

Thanks again :-)
--
Nil Satis Nisi Optimum

Thank you again:-)
--
Nil Satis Nisi Optimum


"Bob Phillips" wrote:

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

Bob

"Michael" wrote in message
...
Thanks Bob.

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

Thanks again :-)
--
Nil Satis Nisi Optimum