MrExcel Publishing
Your One Stop for Excel Tips & Solutions

how to call in VBA a Worksheets whit variable?

Posted by Walter on December 19, 2001 10:40 PM

Very good and helpFULL site.Thanks.
I want to call in VBA my worsheet instead of his name
by a vaiable declared before.So i can change de script
to certains pages and no to cut and paste the same code for each page??

Posted by alfredo sanchez on December 20, 2001 12:53 AM


i think you can use this code:

Sub aver()
Dim nam As Integer
nam = 1 'this is the variable you can use to
select the sheet
End Sub

Posted by walter on December 20, 2001 1:08 AM

Re: how to call in ... Gracias BUT NO'T

EVEN I tried a function
but not~

Posted by Joe Was on December 20, 2001 10:43 AM

You can reference a named worksheet by its number even if you re-named it. The shet number is the tab order from left to right, but in some cases it may be in creation order. if I whant to pull a sheet by number and not name I test the code with a Display sample to test if I get the sheet I want. Once I know that sheet1 is infact sheet 1 then I code "Worksheets("Sheet1") even if sheet 1 is named "Menu." JSW

Posted by Cressida on December 20, 2001 3:51 PM

Clarification ......

You said "Once I know that sheet1 is infact sheet 1 then I code Worksheets("Sheet1") even if sheet 1 is named "Menu."
I think you meant to say Worksheets(1) instead of Worksheets("Sheet1").

Worksheets("Sheet1") specifies the sheet named Sheet1.
If Sheet1 were the first sheet,then Worksheets(1) could be used instead of Worksheets("Sheet1").
If sheets are moved or added or deleted, the number that refers to a particular sheet can change.