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 |
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 |