Automating VB Script, copy range of data from multiple workbooks into one workbook

[ratsmdj]

Hello All,

I have been after this project for quite some time and I feel that someone on here with the correct knowledge will be able to assist me. My googlefu is not good enough for VBA projects I guess. I know this will be long winded but please bear with me. I am looking to make a VBA macro script that will download data across multiple books and import them into a master book. From there I can just cut and paste the relevant data to another master book (would be nice to just import it directly to the last book but I know that is asking for a lot so I am ok with just going to the first master and ill just hand copy to the 2^nd master)

The work books are sent every week, there is one folder lets call it USA and then from there, there are 50 states so 50 subfolders within the USA folder.

USA/AL
USA/AK
USA/AR

So on and so forth, in each sub folder there are years, 2016-2019 (where all the old sheets I’ve already downloaded and inputted into the master sheet goes, we can ignore these). The rest of the subfolders are basically monthly reports so for lets say April there will be a total of 4 subfolders named by their state-code-date (example: AL065-00704032019) each week the number AL065 will jump up one these are all downloaded auto magically. So for April my subfolder for USA/AL will look like so:

USA/AL/2016 (ignore)
USA/AL/2017 (ignore)
USA/AL/2018 (ignore)
USA/AL/2019 (ignore)
USA/AL/AL065-00704032019
USA/AL/AL066-00704132019
USA/AL/AL067-00704232019
USA/AL/AL068-00704302019

In each folder it consists of 3 files, 1 .xls (ignore) 1 .xlsx (the data I want is from this sheet) and 1 .pdf that confirms the data on .xlsx is true and correct

So USA/AL/AL065-00704032019 will have the following in its folder:

AL0065-00704032019-PLSC.xls
AL0065-00704032019-PLCRR.XLSX
AL0065-00704032019-PLCR.PDF

After looking at the structure of the folders and deducing what I needed, I will need the VBA script to do the following. I don’t care if I have to run the macro once in every state subdirectory at least I wont have to open files and cut/paste into the master sheet. But in short I need the functions:
Access the

USA directory

Scan through and ignore all the years sub folders. Open only the newest directory:

USA/AL/AL065-00704032019
USA/AL/AL066-00704132019
USA/AL/AL067-00704232019
USA/AL/AL068-00704302019

So in this case the folder would be USA/AL/AL068-007040302019 (4/30/2019 will be the last date modified shown in the operating system file explorer window)

Scan through the folder and open the one .xlsx file and copy sheet: “Monthly” cell range: C6:F11

Ignoring any blank cells or anything that starts with “Enter Voyage Number”

​

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

_{^{<strike>
</strike>}}[/FONT]
So the VBA script will open up and copy cells C6-G7 respectively and any entry after labeled “Enter Voyage Number” will be excluded.

This will then put data into a master sheet that I can just open up and all values from all 50 folders will already be in one sheet for me to copy into a another sheet.

I have banged by head doing this multiple ways and it does not work, some scripts ive modified doesn’t work, or some will require me ot manually select the file myself.

Thank you please let me know if you have any questions

 

[ratsmdj]

Shouldn't make any difference.

Im sorry if i am not explaning it correctly, the paste script above works now and thank you for that, i was close to it but i banged my head at the wall for a good 2 hours on it and all it was was a simple line break.

What I am asking is that normally on a normal month all rows C6-F11 will be filled, however in a short month there will only be one row of data. from C6-F6, i dont want the script to copy the rest of the data going from C7-F11 is there a way to ignore blank values? The place holder for C6-C11 is "Enter Voyage Number" but sometimes the place holder may be blank as well. Which is why i am asking. Thank you again!

 

[ratsmdj]

Im sorry if i am not explaning it correctly, the paste script above works now and thank you for that, i was close to it but i banged my head at the wall for a good 2 hours on it and all it was was a simple line break.

What I am asking is that normally on a normal month all rows C6-F11 will be filled, however in a short month there will only be one row of data. from C6-F6, i dont want the script to copy the rest of the data going from C7-F11 is there a way to ignore blank values? The place holder for C6-C11 is "Enter Voyage Number" but sometimes the place holder may be blank as well. Which is why i am asking. Thank you again!

Anyone with any thoughts on how to achieve this?

 

[Fluff]

Firstly please be patient, we are all volunteers & give what time we have freely.

Secondly, If the cells are blank it doesn't matter if you copy them across or not.

 

[ratsmdj]

Fluff, i am sorry, i didnt mean to offend any one etc.

But in short the data really isnt short, it gets its values from the other sheet.

This is what it looks like: (the row#s should start @ 6 and not 1 as per the image below, so instead of showing 1-6 its 6-11)

And this is the output with your help of course:

If you note there is a lot of data i do not need that i will have to manually go in and delete.

Thanks for all your help thus far and any other help you may provide

Thanks