Select a sheet by name in a macro

[swatsp0p]

Is it possible to reference a sheet by it's tab name (e.g. "Jan 06")
instead of its sheet number (e.g. "Sheet2")?

Details:

I am using the following code to populate a template on Sheet1 with
data on Sheet2. I am using the result of the macro to set a lookup
value in the template (then using vlookups to populate the cells of the
template), printing the page and cycling to the next value to the end of
the data on Sheet2.


Code:
--------------------
Private Sub CommandButton1_Click()
On Error Resume Next

Dim I As Integer
Dim J As Integer
Dim rgLastCell As Range
Set rgLastCell = Sheet2.Range("A65536").End(xlUp)
J = rgLastCell.Value

For I = 1 To J

Range("LINE").Value = I
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next I

End Sub

--------------------


Sheets are named such as Jan 06, Feb 06, Mar 06, etc. I would like to
allow the user to input the desired sheet name (e.g. "Jan 06") in a
cell on the Template sheet (e.g. Y4) to use for the data (other than
Sheet2). The user probably won't know the true sheet number.

How would I rewrite the line:
Set rgLastCell = Sheet2.Range("A65536").End(xlUp)

to be:

Set rgLastCell = sheet name "Contents of
Sheet1!Y4".Range("A65536").End(xlUp)?

Any help would be appreciated.

Thanks

Bruce

 

[aidan.heritage]

both of your example are names!

sheets("Jan 06")

is valid as is

sheets(1)

so for your example

Set rgLastCell = Sheets(Sheet1!Y4.value).Range("A65536").End(xlUp)

should be fine PROVIDING that sheet1!Y4 has a valid sheet name (so
build in validation)

 

[Guest]

yes

Set rgLastCell = Sheets("Jan 06").Range("A65536").End(xlUp)

should work fine.

 

[swatsp0p]

Aidan: Thanks for your response.

Tom: you are correct, Sheets("JAN 06") works fine, however
Sheets(Sheet1!Y4.value) doesn't.

How can I get the value from Y4 ("Jan 06") on sheet1 into the code s
it will read from Jan 06? I don't want the user having to edit th
macro each time they want to print from a different data set fro
another sheet.

Is it because "Jan 06" is a TEXT entry, not a numeric entry?

Thanks for your guidance on this

 

[Dave Peterson]

I'd use:

sheets(sheets("sheet1").range("Y4").value).whateveryou'redoinghere

 

[swatsp0p]

Thanks, Dave. That is the syntax I was looking for. Now all is well.

Thanks to all that offered assistance!!

Cheers!

Bruce