Unfortunately, this takes me back to my original problem.... What I was
trying to do was to create different templates for different scenarios (like
1 Part 1 Color, 1 Part 2 Colors, 1 Part 3 Colors, 1 LH Part 1 RH Part, 2LH
Parts 2 RH Parts) where they could track defects and production for
individual parts and then consolidate/roll-up the information several
different ways. They would make a copy of the correct template for the
specific scenario they needed, then re-name the tabs with individual part
numbers both for their ease of reference and to be printed as part of their
page headers. Unfortunately, re-naming the tabs doesn't update the name in
the macro/VB.... The people who will be using the templates are NOT
technical at all, and I was hoping to be able to use the generic sheet names
("(Name)" in the Properties box) in the Consolidate command so the code would
work no matter what the sheets were named and they wouldn't have to update
the code with the newly re-named tabs. If I re-name as you suggested, I'm
not able to personalize the sheets the way I had hoped....

Any other suggestions?

"Jake Marx" wrote:

Yes, that's what I did - sorry I forgot to mention that. If you go to the
VBE and select the worksheet in the Project window, you can change the
(Name) property in the Properties window. That is what controls the
Codename.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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


ckrogers wrote:
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