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

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

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

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

Hi,

I guess I'm not sure I follow what you're trying to do. In my example, I
changed the Codenames of the worksheets to wsP1C1 and wsP1C2. The code does
use the Codenames and not the Worksheet (UI) names, so even if the user
changes the names of the Worksheets through the Excel UI, the code should
still work. That is, unless they make a copy of the original sheet, in
which case Excel will generate a new Codename for that new sheet.

You can give the Worksheets whatever Codenames are best for your situation -
you would just replace the "wsP1C1" and "wsP1C2" in my example with the
Codenames you used.

Does this make sense, or am I still missing the point?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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


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

Hi, Jake. Maybe I'm doing something wrong ... but when I changed the Name
parameter in the Properties window of VA, it physically changed the name on
the tab in my Excel spreadsheet?? It was the "Name" and not the "(Name)"
parameter I was supposed to change, right?

P.S. Thanks so much for spending this much time with me ... I really
appreciate it!

"Jake Marx" wrote:

Hi,

I guess I'm not sure I follow what you're trying to do. In my example, I
changed the Codenames of the worksheets to wsP1C1 and wsP1C2. The code does
use the Codenames and not the Worksheet (UI) names, so even if the user
changes the names of the Worksheets through the Excel UI, the code should
still work. That is, unless they make a copy of the original sheet, in
which case Excel will generate a new Codename for that new sheet.

You can give the Worksheets whatever Codenames are best for your situation -
you would just replace the "wsP1C1" and "wsP1C2" in my example with the
Codenames you used.

Does this make sense, or am I still missing the point?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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


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

ckrogers wrote:
Hi, Jake. Maybe I'm doing something wrong ... but when I changed the
Name parameter in the Properties window of VA, it physically changed
the name on the tab in my Excel spreadsheet?? It was the "Name" and
not the "(Name)" parameter I was supposed to change, right?

Ah, that's the problem. You should change the "(Name)" property, which
corresponds to the sheet's Codename. The "Name" property corresponds to the
sheet's name in Excel (the one displayed on the worksheet tab and changeable
by the end user).

P.S. Thanks so much for spending this much time with me ... I really
appreciate it!

No problem - glad to (try to) help out!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Oh my gosh, Jake. It's a thing of beauty!! It's working exactly like I
wanted it to now ... thanks so much for your help!
Cindy

"Jake Marx" wrote:

ckrogers wrote:
Hi, Jake. Maybe I'm doing something wrong ... but when I changed the
Name parameter in the Properties window of VA, it physically changed
the name on the tab in my Excel spreadsheet?? It was the "Name" and
not the "(Name)" parameter I was supposed to change, right?

Ah, that's the problem. You should change the "(Name)" property, which
corresponds to the sheet's Codename. The "Name" property corresponds to the
sheet's name in Excel (the one displayed on the worksheet tab and changeable
by the end user).

P.S. Thanks so much for spending this much time with me ... I really
appreciate it!

No problem - glad to (try to) help out!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Hi Cindy,

ckrogers wrote:
Oh my gosh, Jake. It's a thing of beauty!! It's working exactly
like I wanted it to now ... thanks so much for your help!

Excellent - glad to hear it. And glad to help, too.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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