Variant Array

[Steph]

Hi everyone. I have a consolidate function that I'm trying to clean
up and hopefully make run faster. 2 things:

1. How can I reference an array instead of filling the array of the
consolidate function with every sheet I want to consolidate? I have
an array already created with the sheet names I want consolidated.
But when I tried to reference that in the consolidate code, it errored
out.

2. I have the range set as R5C5:R500C133. R500 is simply a big
enough area to ensure I am grabbing everything. I don't need it to be
that big, but the size will vary over time. How can I reference the
exact range rather than arbitrarily grabbing 500 rows?

Here's my code. Thanks!

Set shtarray = Sheets(Array("LMU", "Kit", "SMLC", "WLG", "SMLC Cab",
"Serv Cab", "Ntwk Kit", _
"TDAX", "EMS", "SCOUT", "Dir Coup"))

'Consolidate2
Forecast.Range("A5").Consolidate Sources:=Array( _
"'LMU'!R5C5:R500C133" _
, "'Kit'!R5C5:R500C133" _
, "'SMLC'!R5C5:R500C133" _
, "'WLG'!R5C5:R500C133" _
, "'SMLC Cab'!R5C5:R500C133" _
, "'Serv Cab'!R5C5:R500C133" _
, "'Ntwk Kit'!R5C5:R500C133" _
, "'TDAX'!R5C5:R500C133" _
, "'EMS'!R5C5:R500C133" _
, "'SCOUT'!R5C5:R500C133" _
, "'Dir Coup'!R5C5:R500C133" _
), Function:=xlSum, TopRow:=False, LeftColumn:=True,
CreateLinks:=False

 

[Vasant Nanavati]

It's always helpful if you state what error you get rather than just saying
it "errors out".