VBA Array - copy multiple ranges from Workbooks?

[MagicalHippo]

I am a bit confused on how to attempt this as my array knowledge is somewhat limited. Initially, I thought about creating a loop to loop through workbooks and copy the ranges....but realized the copied ranges need to be dumped into their own sheets.

How would I open multiple workbooks, run the same code on all of them, and copy range to lastrow and put it into an array? (i.e. Open WB1, copy range into array, open WB2, copy range into an array. Then paste WB1 to Sheet1, and WB2 to sheet2 etc.)

Thanks

 

[Leith Ross]

Hello MagicHippo,

From what you posted, the array is an unnecessary addition to the macro. Once you have determined the range to be copied, it can then be pasted into it's corresponding worksheet.

 

[MagicalHippo]

Hello MagicHippo,

From what you posted, the array is an unnecessary addition to the macro. Once you have determined the range to be copied, it can then be pasted into it's corresponding worksheet.

Hi Leith,

That is what I had figured, I was just challenging myself to see if we could somehow incorporate everything into an array :P

 

[Leith Ross]

Hello MagicalHippo,

You can store the Range values in an Array but it is a lot more work. After all, Ranges are simply 2-D arrays. You would either need to create a static array large enough to hold all the other Ranges or create a dynamic array.

The problem with a static array is you need to have some idea of the maximum array size when you create it.

Dynamic arrays only allow you to expand the last subscript of of the array. In the case of a 2-D array, that would be the columns on the worksheet. As a workaround, you could transpose the Range and resize the dynamic array. You would have to do a final transpose on the array before copying it to the worksheet.

If you really are interested in learning about dynamic arrays, I can pull some code together for you.

 

[MagicalHippo]

Hello MagicalHippo,

You can store the Range values in an Array but it is a lot more work. After all, Ranges are simply 2-D arrays. You would either need to create a static array large enough to hold all the other Ranges or create a dynamic array.

The problem with a static array is you need to have some idea of the maximum array size when you create it.

Dynamic arrays only allow you to expand the last subscript of of the array. In the case of a 2-D array, that would be the columns on the worksheet. As a workaround, you could transpose the Range and resize the dynamic array. You would have to do a final transpose on the array before copying it to the worksheet.

If you really are interested in learning about dynamic arrays, I can pull some code together for you.

Leith,

I ended up doing this task the manual way, but for future I would love to learn more on dynamic and static arrays. I find they're my weak point in VBA programming, would love some short examples. If you don't mind!