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)

swatsp0p wrote:
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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=549966