Copying cells from multiple workbooks into a new workbook

I've searched and tested for a few hours now and can't seem to figure this out. I'm new to VBA so I can't custom create my own code.

I have a folder of macro-enabled workbooks with non-standard naming. In each source workbook there is a worksheet called "Summary pg 1" that is a standard template form. I would like to have Cells G2, A14, A16, C17 and A19 from each workbook copied into its own row in a new workbook.

SourceWorkbook1 to DestinationWorkbook1
G2 = A1
A14 = B1
A16 = C1
C17 = D1
A19 = E1

SourceWorkbook2 to DestinationWorkbook1
G2 = A2
A14 = B2
A16 = C2
C17 = D2
A19 = E2

Any help you can throw my way is highly appreciated.

Put this macro in a regular module of your master file. The macro uses "C:\Test\" as the location of your other files. Change this path to suit your needs. Don't place your master file in the same folder as your other files. Run the macro from your Master file.

Please Login or Register  to view this content.

Thanks for the reply. Here's what I put inside the module:

Please Login or Register  to view this content.

I bolded the changes I made. I changed the path, the name of the Master spreadsheet and the destination sheet as Sheet2 instead of Sheet1.

When I clicked the 'run' button, it processed for about a second then quit. No errors were generated and it did not update the master spreadsheet. The master spreadsheet is on the desktop and not inside the folder containing the source files. I have to enable macros on each of the source files as I open it, does that matter?

Thoughts?

I created a couple of dummy source files and saved them in the same folder. I saved the master file on the desktop. It worked in my tests. Try having only the source files in your folder. It looks like you have a file named "Aerosol Master List.macro" saved in the folder with the other source files. Move this file to another location. Delete the line

Please Login or Register  to view this content.

and delete the "End If" that goes with it and give it another try.

Originally Posted by Sean Thomas

Hi
if you click the button on sheet 1, it will open up a browse for folder screen, select the folder that contains the files to collate, then click ok.
it will open each workbook, check for sheet(Summary pg 1) and then copy data into sheet1, close the source workbook and then open the next workbook etc.

i've tested it and it works

Thanks, this worked great! Saved me a ton of time!!

Originally Posted by Mumps1

I created a couple of dummy source files and saved them in the same folder. I saved the master file on the desktop. It worked in my tests. Try having only the source files in your folder. It looks like you have a file named "Aerosol Master List.macro" saved in the folder with the other source files. Move this file to another location. Delete the line

Please Login or Register  to view this content.

and delete the "End If" that goes with it and give it another try.

I double checked and I have only the source files in the folder. I removed the parts you outlined and it still did not pull in the data from the source files. Although the solution posted by Sean Thomas worked, I'm kind of curious why this didn't.

Originally Posted by Sean Thomas

Hi Mumps,
I tried your code, really like how it loops through the files, haven't seen that method before.
I also had a few problems but mainly due to finding last used row on master sheet.
made some minor changes and works well.

Please Login or Register  to view this content.

I just tried this by substituting the appropriate information and this generated a "208" dialog box with an OK button. Held the enter key so it cycled through each dialog and still no data input!

@bigalpha: I'm glad you've got it working.

@Sean Thomas: Thank you, Sean for your solution.

I changed:

1. the path of my source folder
2. "Master" to "Aerosol Master List.macro" [The code didn't work even if I left "Master" in place]
3. "sheet1" to "sheet2" [I already have data on Sheet1]

Absolutely! This is the first set of code you provided that I changed (this includes removing the IF statement)

Please Login or Register  to view this content.

This is the revised code you provided:

Please Login or Register  to view this content.

Okay, I made the changes to the initial set of code you provided and it worked great except that I received

Please Login or Register  to view this content.

instead of copying the value of the formula.

The updated version of code provided did not work - it opened the source sheets but then gave a dialog box of "208".

Originally Posted by Sean Thomas

Sorry you have lost me.
where did you get that file path from? value of what formula?

Error 208 is not a VBA error code,
What causes error 208 ?The 208 error may be caused by windows system files damage. The corrupted windows system files entries can be a real threat to the well being of your computer.

There can be many events which may have resulted in the system files errors. An incomplete installation, an incomplete uninstall, improper deletion of applications or hardware. It can also be caused if your computer is recovered from a virus or adware/spyware attack or by an improper shutdown of the computer. All the above actives may result in the deletion or corruption of the entries in the windows registry. This corrupted registry will lead to the missing and wrongly linked information and files needed for the proper working of the application.

The field is a formula,

Please Login or Register  to view this content.

, that pulls in a piece of data from a different sheet . That formula pulls in text like this: "Aervoe, Fleet & Farm Multiple colors".
The code provided by mumps1 pulled in the text instead of the formula.

I'm on a government workstation, if that matters.