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?