Extract and transpose specific cell data from multiple files

What do you mean by "update"? Normally running the macro would open all 2000 files again and reimport again.

So, a clean reimport each time it occurs... so the macro simply does the same thing in full each time?

I ask because the macro could also record the full path/name of the file the data was taken from, say we record that in column Z. Then when the macro is run a second time, it can see the files that have already been in imported and skip them, only opening new files and adding them to the bottom of the existing collected data.

Sound ok?

This is a common method, base macro is published here.

I've edited that macro for your scenario, you'll need to edit the fPath = ..... line of code to point to the folder where the incoming files are stored. (marked in red below).

The macro will open each file in that path, check to see if a line already exists in the consolidation master sheet by checking the filenames recorded in column Z. IF it exists, it will update that existing row, else it will append the new data/sheet to the bottom of the list.

Each imported file is then moved into an "imported" folder. Lather, rinse, repeat.

Please Login or Register  to view this content.

Originally Posted by Mad-Dog

Basically I opened a VBE, dropped your code in, and clicked run......

The parts of the code YOU have to edit are marked in red. The code is commented so you know what each line of code is doing, so the red lines are explained as well.

Hope this attachment helps??

Basically I have circa. 2,000 files similar to the one I have attached and the folder will grow.....So I am looking to extract the data in the "Cover Page" tab cells highlighted in YELLOW into a new "Master" single spreadsheet (a list of the folder).

So in the "Cover Page" tab they are in a column but in the report of all the files in the folder the data would obviously need to be in a ROW per file in the folder.

Am I making sense? I feel that your code looks good and will work but it's likely I am fluffing the minor tweak it needs....

Here is an updated file that I am trying to extract the data from....YELLOW tab is what the output should look like in a separate file. Green tab (Cover Page) is where I am trying to extract the data from....All the files in the folder will be named differently, however, have the same format i.e 3 tabs

Would be great to be able to open a blank workbook, select the macro, that then asks to select the folder and run it....and bob's your uncle out pops a report with 2,000+ rows of data extracted as per the YELLOW tab called example..

Your perseverance will be greatly appreciated..

Happy to Skype if you agree and are willing...

Macro updated, YOU still need to make sure the name of your actual consolidation worksheet is named properly under wsMaster = ......
And you need to make sure you put in the correct fPath to the folder on your system where the files will be found. I saw no macro in your workbook so no idea if you understand these edits or not.

I've expanded the macro to copy all 15 supplier cells, so all data will be collected now if it exists. Fixed the error you were seeing, too.

Please Login or Register  to view this content.