Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to go to select sheet | Excel Discussion (Misc queries) | |||
macro sheet select | Excel Discussion (Misc queries) | |||
MACRO TO SELECT A PARTICULAR SHEET! | Excel Programming | |||
Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions | |||
Help! Macro to select all sheet from AA to ZZ | Excel Programming |