Hi, Jake. Thanks for your help.
When I copied and pasted your code and tried to run it, I'm now getting the
error
Run-time error '424':
Object required
When I debug, it highlights the whole "Range" section through
"CreateLinks:=False". It looks to me like maybe you renamed Sheet1 to
'wsP1C1' and Sheet2 to 'wsP1C2' and since I didn't, it doesn't find the
correct object name? Is that right? If so, how do I do that? I
right-clicked on the object names, but nothing looks like a rename option to
me....

"Jake Marx" wrote:

Typically, you could just use the Codename for the worksheet you are
referring to.

Unfortunately, the string the Consolidate method is expecting must be
specifically formatted. So here's one way to use the Codenames and still
get the string in the correct format:

Sub Consolidate_LH_RH()
Range("Consolidate_ALL").Consolidate Sources:=Array( _
msConvRngToR1C1(wsP1C1.Range("A29:AF46")), _
msConvRngToR1C1(wsP1C2.Range("A29:AF46"))), _
Function:=xlSum, TopRow:=False, LeftColumn:=True, _
CreateLinks:=False
ActiveWindow.SmallScroll Down:=23
Range("O61").Select
End Sub


Private Function msConvRngToR1C1(rrng As Range) As String
msConvRngToR1C1 = "'" & rrng.Parent.Name & "'!" & _
rrng.Address(ReferenceStyle:=xlR1C1)
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


ckrogers wrote:
I'm having trouble with the syntax for using generic sheet names in a
macro/VBA in Excel. My working code follows where Sheet1='Part 1
Color 1' and Sheet2='Part 1 Color 2'. I've tried using Sheet1 and
Sheets(1) in every possible format I can think of -- with and without
apostrophes, quotes, brackets, etc. -- but I keep getting error
messages. Any and all help will be appreciated!!

CODE:
Sub Consolidate_LH_RH()
'
' Consolidate_LH_RH Macro
' Macro recorded 12/18/2004 by Cindy Rogers
'

'
Range("Consolidate_ALL").Select
Selection.Consolidate Sources:=Array( _
"'Part 1 Color 1'!R29C1:R46C32" _
, _
"'Part 1 Color 2'!R29C1:R46C32" _
), Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=False
ActiveWindow.SmallScroll Down:=23
Range("O61").Select
End Sub