VBA to copy data from multiple workbooks into new master workbook

[MSpicewood]

Good morning,


I'm a VBA novice and having a hard time with my first major project. I've been researching solutions to copy all the data from multiple files (.XLS?) in a directory called "C:/allfiles/" to a newly created master report file.

I need a solution that won't capture any additional blank rows as each file has a different number of entries. I'm also trying to only pull from the first worksheet on each workbook file. These files have a second tab called LOG which I'm trying to exclude.

I have referenced a previous topic here to get started but it's not working out as my specifications are a bit different. Referenced: https://www.mrexcel.com/forum/excel...ata-multiple-workbooks-into-master-sheet.html

Any help is greatly appreciated. Thank you.

 

[Fluff]

Do any of your files have a blank rows within the data?

 

[MSpicewood]

Let me start by correcting myself: Header row 1 from file each shows as in Header row 1 (file 1), Header row 1 (file 2), Header row 1 (file 3) etc.
I can confirm other than the header most of these rows have a blank A column. To test the macro, I've added random numbers to the A column and ran it again. I can confirm that the output does include the missing rows when column A isn't blank.

So the way CurrentRegion is interacting with the loop is causing an issue?

 

[Fluff]

In that case change the highlighted A to a column that will always have a value

Wbk.Sheets(1).Range("[COLOR=#ff0000]A[/COLOR]1").CurrentRegion.Copy

 

[MSpicewood]

I've done as requested and it's still having difficulties. I've located a secondary issue caused by the offset number which seems to be the second piece of the puzzle.

Wbk.Sheets(1).Range("F1").CurrentRegion.Copy Mws.Range("A" & Rows.Count).End(xlUp).Offset([COLOR=#ff0000]1[/COLOR]) 'Original sort and copy

It seems to be cutting off the majority of the data in most files except the last file.
I've compensated by changing the value to ten. As a result I can see all of the data finally!

This offset change doesn't fix the version of the sort and copy that removes the headers. It also leaves a bunch of blank spaces BUT since I made that post-loop header cleaner it will do a rough version of removing headers. I can then create something that goes through the master work sheet and removes the entire row if A-F are blank, thus tidying the sheet further.

What do you think?

 

[Fluff]

Assuming col F will always have data for every row try

Mws.Range("[COLOR=#ff0000]F[/COLOR]" & Rows.Count).End(xlUp).Offset([COLOR=#ff0000]1,-5[/COLOR])

 

[MSpicewood]

Assuming col F will always have data for every row try

Mws.Range("[COLOR=#ff0000]F[/COLOR]" & Rows.Count).End(xlUp).Offset([COLOR=#ff0000]1,-5[/COLOR])

That works perfectly. I'll take the time to look through the code again until I understand it all from top to bottom so that my future tools can be written independently and time effectively moving forward. Thank you for everything.

 

[Fluff]

Glad to help & thanks for the feedback