Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Sheet Names
I'm creating a spreadsheet with a macro to consolidate the data on individual
worksheets. The spreadsheet will be used as a template and copied as required for different scenarios; the worksheets will then be renamed for specific part numbers. Is there a way I can identify the worksheets with their generic names (Sheet1, Sheet2, etc.) in the Visual Basic code of the macro so they will still work even when the individual sheets are renamed? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Sheet Names
refer to the sheets by index number eg
sheets(1) sheets(2) sheets(3) the index number is the order in which they were created in the new workbook sheets(1).name will return a name you can work with or when you rename your sheets assign it to a variable sheet1.name = "Yourname" yourn = sheet1.name sheets(yourn).range("a1").value = yourn "ckrogers" wrote: I'm creating a spreadsheet with a macro to consolidate the data on individual worksheets. The spreadsheet will be used as a template and copied as required for different scenarios; the worksheets will then be renamed for specific part numbers. Is there a way I can identify the worksheets with their generic names (Sheet1, Sheet2, etc.) in the Visual Basic code of the macro so they will still work even when the individual sheets are renamed? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Sheet Names
Hi,
Just to clarify - Sheets(1) will return the first sheet in a Workbook, not the first sheet that was created in that Workbook. So if the user changes the order of the sheets, you will get unexpected results with this method. You can refer to the Codenames of the Worksheets, which will remain the same even if the user (or you) changes the sheet name. The Codename is displayed in the VBE Project window (under Microsoft Excel Objects). If you select the sheet icon for the desired Worksheet, you can modify the (Name) property in the Properties window. I typically use "ws" as a prefix - ie, "wsData", "wsAssumptions", etc. Then, in your code, you can use it like you would an object reference: wsData.Range("A1").ClearContents MsgBox wsAssumptions.Name -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] ckrogers wrote: I'm creating a spreadsheet with a macro to consolidate the data on individual worksheets. The spreadsheet will be used as a template and copied as required for different scenarios; the worksheets will then be renamed for specific part numbers. Is there a way I can identify the worksheets with their generic names (Sheet1, Sheet2, etc.) in the Visual Basic code of the macro so they will still work even when the individual sheets are renamed? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Sheet Names
Thanks so much for your help. Here's my actual code ... I'm not sure how to
incorporate the your logic?? In the code following Sheet1 = 'Part 1 Color 1'; Sheet2 = 'Part 1 Color 2'; Sheet3 = 'Part 1 Color 3' Sub Consolidate_LH() ' ' Consolidate_LH Macro ' Macro recorded 12/18/2004 by Cindy Rogers ' ' Range("Consolidate_1").Select Selection.Consolidate Sources:=Array( _ "'Part 1 Color 1'!R29C1:R46C32" _ , _ "'Part 1 Color 2'!R29C1:R46C32" _ , _ "'Part 1 Color 3'!R29C1:R46C32" _ ), Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False ActiveWindow.SmallScroll Down:=23 Range("O61").Select End Sub "Jake Marx" wrote: Hi, Just to clarify - Sheets(1) will return the first sheet in a Workbook, not the first sheet that was created in that Workbook. So if the user changes the order of the sheets, you will get unexpected results with this method. You can refer to the Codenames of the Worksheets, which will remain the same even if the user (or you) changes the sheet name. The Codename is displayed in the VBE Project window (under Microsoft Excel Objects). If you select the sheet icon for the desired Worksheet, you can modify the (Name) property in the Properties window. I typically use "ws" as a prefix - ie, "wsData", "wsAssumptions", etc. Then, in your code, you can use it like you would an object reference: wsData.Range("A1").ClearContents MsgBox wsAssumptions.Name -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] ckrogers wrote: I'm creating a spreadsheet with a macro to consolidate the data on individual worksheets. The spreadsheet will be used as a template and copied as required for different scenarios; the worksheets will then be renamed for specific part numbers. Is there a way I can identify the worksheets with their generic names (Sheet1, Sheet2, etc.) in the Visual Basic code of the macro so they will still work even when the individual sheets are renamed? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generic Sheet Names
Thanks so much for your help. Here's my actual code ... I'm not sure how to
incorporate the your logic?? In the code following Sheet1 = 'Part 1 Color 1'; Sheet2 = 'Part 1 Color 2'; Sheet3 = 'Part 1 Color 3' Sub Consolidate_LH() ' ' Consolidate_LH Macro ' Macro recorded 12/18/2004 by Cindy Rogers ' ' Range("Consolidate_1").Select Selection.Consolidate Sources:=Array( _ "'Part 1 Color 1'!R29C1:R46C32" _ , _ "'Part 1 Color 2'!R29C1:R46C32" _ , _ "'Part 1 Color 3'!R29C1:R46C32" _ ), Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False ActiveWindow.SmallScroll Down:=23 Range("O61").Select End Sub "ben" wrote: refer to the sheets by index number eg sheets(1) sheets(2) sheets(3) the index number is the order in which they were created in the new workbook sheets(1).name will return a name you can work with or when you rename your sheets assign it to a variable sheet1.name = "Yourname" yourn = sheet1.name sheets(yourn).range("a1").value = yourn "ckrogers" wrote: I'm creating a spreadsheet with a macro to consolidate the data on individual worksheets. The spreadsheet will be used as a template and copied as required for different scenarios; the worksheets will then be renamed for specific part numbers. Is there a way I can identify the worksheets with their generic names (Sheet1, Sheet2, etc.) in the Visual Basic code of the macro so they will still work even when the individual sheets are renamed? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
Generic Worksheet Names | Excel Discussion (Misc queries) | |||
Cell names = sheet names | Excel Worksheet Functions | |||
I want to print out the sheet tabs (sheet names) | Excel Worksheet Functions | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming |