Macro to pull data from another file.

Hey I am fairly new to programming in excel. I need to create a macro that will go to multiple other spreadsheets (files) and gather certain data (from cells I27, j27, k27) and bring it back to my current spread sheet (and paste to cells c65, d65,e65). For example every day the same spreadsheet is filled out and saved by the date (11-18-10). I need to be able to create a one spreadsheet to copy data from certain cells in each of those sheets and copy them into a yearly spreadsheet with each row as a date and paste into cells c, d, e of that date row. I can use either a button or a GUI. I was trying just to use a button that would go by row and update per date.

Hello eface2face,

Welcome to the Forum!

I want to make sure I understand you correctly. You want to create a master workbook that contains the information from another workbook named as a date, like 11-18-10.

Please let me know if any of these assumptions is incorrect.

1. The information from this workbook is in cells "I27:K27" of each its worksheets.
2. This information will be copied over to the Master workbook starting with cells "C65:E65" and the next day to cells "C66:E66" etc.

Hello YellowPower,

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code using the # icon

1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] After the last line.

Originally Posted by eface2face

I have to say that it is hard to explain in words. I hope I didn't make it worse. please let me know.

That's why sample workbooks are the best visual aid. You can post up your master workbook and a couple of your "data" workbooks you want to pull from, then it would be obvious what you want to appear where, yes?

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

Hello eface2face,

Now that I have downloaded the workbooks, I am confused about what data goes where. Looking at the previous posts nothing matches up. You also have formulae on the "Data" worksheet in the "Master" workbook and there are no column headers. WTF?

Give this a try:

Please Login or Register  to view this content.

Note the fPath and the fDone strings...

fPath is the directory where the files are found, remember the final \ in that string

fDone is where the files are moved to after they are imported so you know they are done. Create that directory if needed.

Errors are buffered and presented as a list of files that were not processed at the end. Try putting a garbage file in that directory that isn't named for a date and you'll see.

Jerry

That code works great. I wanted to know how to stop the macro from deleting the daliy sheets. I ran the macro and the data was copied into the master sheet like it is supposed to, but it deletes the sheet where the data comes from. I need to make sure those daily sheets don't get deleted or consolidated. also how do I change the font of the collected data in the Macro?

Did you follow the instructions given in post #11? The macro is looking for an IMPORTED folder inside your data folder, the files are to be moved into there as they are imported to preclude duplicate imports.

Set the Master sheet columns to the fonts you want, then we'll change the macro to not transfer anything but values.

Please Login or Register  to view this content.

Jerry

Thank you so much for your help. Sorry that I was a pain in the butt. Next time I will be able to better explain myself. Thanks for spending the time to help me