Select a sheet by name in a macro
 

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

Select a sheet by name in a macro
 

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

Select a sheet by name in a macro
 

yes

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

should work fine.

--
Regards,
Tom Ogilvy

"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

Select a sheet by name in a macro
 

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

--
swatsp0

-----------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...fo&userid=1510
View this thread: http://www.excelforum.com/showthread.php?threadid=54996

Select a sheet by name in a macro
 

I'd use:

sheets(sheets("sheet1").range("Y4").value).whateve ryou'redoinghere


swatsp0p wrote:

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 so
it will read from Jan 06? I don't want the user having to edit the
macro each time they want to print from a different data set from
another sheet.

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

Thanks for your guidance on this.

--
swatsp0p

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

--

Dave Peterson

Select a sheet by name in a macro
 

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

Thanks to all that offered assistance!!

Cheers!

Bruce


--
swatsp0p


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