Using an array in Consolidation

S

Steph

Hello everyone. I was hoping you could help with a relatively simple
problem. I am using the consolidation function in VBA, and have the
following code:

' Forecast.Select
' Range("B5").Select
' Selection.Consolidate Sources:=Array( _
' "'Sheet1'!R1C2:R10000C17" _
' , "'Sheet3'!R1C2:R10000C17" _
' , "'Sheet5'!R1C2:R10000C17" _
' , "'Sheet7'!R1C2:R1000C17" _
' ), Function:=xlSum, TopRow:=True, LeftColumn:=True,
CreateLinks:=False

I have already created an array for the sheets:
Set shtarray = Sheets(Array("Sheet1", "Sheet3", "Sheet5", "Sheet7"))

How do I use the shtarray variable in the consolidation statement so I
don't have to rename each sheet again? Thannks so much!!

-Steph
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

data consolidation using vba 0
Dynamic Array for data consolidation 2
Consolidation refresh (update) 0
Data Consolidation using dynamic path 1
Consolidation Function VBA 6
Search an array of sheets for an array of numbers & return count of numbers 11
Data range in Array worksheets 2
Consolidation Problem 0

Top