Copying multiple sheets into one, and running out of rows

[moocow77]

Hi Everyone,

I have been coding for about 3 hours, so very inexperienced! Hopefully a wise owl will be able to help me here!

I have a need to extract a couple of data points out of multiple files (approx. 150). I have only just begun coding but am happy on extracting the data from the source file and dropping it into the next available row of Col A of my master data. As I know I have 2.5 - 3 million lines....I know that one of my extractions will not fit as we reach the limit of excel. Once this is reached, I want it to start again in column E.

I am assuming that I would use Activate.ActiveSheet.UsedRange.Rows.count to see if it fits and then to drop it in accordingly into col A, Col E (or Col I) but I'm a bit confused on how to do this


I have a second problem - I have built this code up using a single file (milk1 !) but how do I get it to cycle through all the files in a single directory?

Thanking you in advance for your help. I have attached my code

MC

[CK76]

Personally I'd recommend switching from VBA to PowerQuery (Get & Transform) method.

1. Query folder using, Get Data -> From File -> From Folder.
2. Use binary combine menu to combine data (apply filter as needed).
3. Load data to data model and check row# count.
4. If it's too large to return data to worksheet, split them into multiple as needed using Table.Split function.
https://docs.microsoft.com/en-us/pow...-m/table-split

[moocow77]

ok 0 let me look at that too....

Thanks