Copy Content of 3 Excel File into another Workbook

Hi All,
I have 4 excel files that I generate each month. Here are the names:
short_aum.xlsx
shortu_aum.xlsx
long_aum.xlsx
longu_aum.xlsx

They are always kept in the same folder: S:\CRetrum\Composite\BuildsforMacro

I would like to have them copy to the following tab in this spreadsheet Composite_Return_Report_TEMPLATE.xlsm
Short
Short Under
Traditional
Traditional Under

I am assuming the macro would reside in the Composite_Return_Report_TEMPLATE.xlsm file, but am not sure if the others can be closed (that would be ideal). I also am not sure if I can keep the remaining tabs left along in this workbook and simply repaste over these tabs.

Any guidance is appreciated!
Thanks all!
Kerry

Thanks so much for the speedy reply.

"Do the generated files all have just one tab and is it named "Sheet1" or must we use Sheets(1)?"
YES, each excel file only has one tab name in it and it is Sheet1.

I tried running this but am assuming I need to add path information somewhere to let it know where to pull the files from?
Also do I add the file extension to the end of each one? I am getting a debug at this line:
Set wb = Workbooks("Composite_Return_Report_TEMPLATE")

Thanks

Thanks again for your hard work - sorry for your frustration Friday!

You are right, none of the spreadsheets will be open (or at least that was my idea) or else the user might as well just open each and copy them over. So what you have done is great here.

However, getting frustrated myself like I am missing something! It all looks like a simple macro, but now when I run it, I still get a debug at this line:
Set ws = Workbooks("short_aum").Sheets(1) "Debug: Script out of Range"

It DOES open the short_aum spreadsheet up which is positive. But then sits there and does not copy it over?

Thanks!
Kerry

That doesn't work either. Still debug on that line.

I might approach this from another way. Thanks for your efforts.

Worked like a charm! Great job! Thanks for all your help!

This is what I settled on...
(FYI to others looking at this, it seemed key to 'close' each spreadsheet after copying from it, or else I got an error about how much I had on the clipboard.)

Please Login or Register  to view this content.