Add to a range array

[mikeymay]

I am wanting to use the following to specify specific default sheets and range that I want to copy to PowerPoint

MyRangeArray = Array(Sheet1.Range("A1:C10"), Sheet4.Range("A1:C10"),  Sheet3.Range("A1:C10"), Sheet2.Range("A1:C10"), Sheet5.Range("A1:C10"))

It may be that the number of sheets will increase dependent on what the suers selects but how do I add to this range?


TIA

 

[tusharm]

Look at the ReDim statement with the Preserve option.

I am wanting to use the following to specify specific default sheets and range that I want to copy to PowerPoint

MyRangeArray = Array(Sheet1.Range("A1:C10"), Sheet4.Range("A1:C10"),  Sheet3.Range("A1:C10"), Sheet2.Range("A1:C10"), Sheet5.Range("A1:C10"))

It may be that the number of sheets will increase dependent on what the suers selects but how do I add to this range?


TIA

 

[Tetra201]

Is this what you need?

Dim MyRangeArray() As Variant
MyRangeArray = Array(Sheet1.Range("A1:C10"), Sheet2.Range("A1:C10"), Sheet3.Range("A1:C10"), Sheet4.Range("A1:C10"), Sheet5.Range("A1:C10"))
ReDim Preserve MyRangeArray(LBound(MyRangeArray) To UBound(MyRangeArray) + 1)
Set MyRangeArray(UBound(MyRangeArray)) = Sheet6.Range("A1:C10")