I have reports done in Excel sent to be by various locations every month. I'd like to combine all these reports into one large report. Is there an easy way to automate this so that I can just click a button each month for this to happen instead of cut and pasting each one into the large report?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Consolidating reports
- Thread starter pliant
- Start date
Excel Facts
Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Barrie Davidson
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 2,330
Okay, please tell me the exact steps you are taking to run the macro.
I looked at the error number "400" and, from what I can tell, Excel is saying it can't open the form (I assume it's the file open form) because it's already open. Does that make sense to you?
I looked at the error number "400" and, from what I can tell, Excel is saying it can't open the form (I assume it's the file open form) because it's already open. Does that make sense to you?
Upvote
0
I pasted the code you supplied into Sheet1 of a workbook i have saved as LargeReport.xls after opening the Visual Basic Editor. Went into Excel and ran the macro. Selected rpt1 the report i want pasted into Sheet1. It then asks me to save it so save it as LargeReport.xls. However it is just a blank sheet. Nothing has been copied over from rpt1.xls.
Upvote
0
Barrie Davidson
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 2,330
pliant said:I pasted the code you supplied into Sheet1 of a workbook i have saved as LargeReport.xls after opening the Visual Basic Editor. Went into Excel and ran the macro. Selected rpt1 the report i want pasted into Sheet1. It then asks me to save it so save it as LargeReport.xls. However it is just a blank sheet. Nothing has been copied over from rpt1.xls.
Okay, try running the macro and select a different file name for the save file name (how about Pliant.xls for now?) when you are prompted to save the file. Confirming, the files you are almagamating are Excel files, right?
Upvote
0
I think I may know part of the problem. this line doesn't seem to be working: Workbooks.Open Filename:=Active_File_Name
If i understand correctly this should open up the file that i had selected to copy from but this file is never opened. i'm not sure why. any ideaS?
If i understand correctly this should open up the file that i had selected to copy from but this file is never opened. i'm not sure why. any ideaS?
Upvote
0
Barrie Davidson
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 2,330
Difficult to troubleshoot this long distance, but here goes (no offense meant here, just want to make sure you're not missing any steps).....
Create a new spreadsheet (let's call it Consolidate.xls. Open the VBA Editor (ALT+F11). In the Project Explorer window (usually top left window), select the VBAProject names Consolidate.xls. From the menu bar at the top, select Insert|Module. Paste my code in the VBA window (usually the window on the right). Close the VBA Editor (ALT+Q). Save Consolidate.xls. Run the macro (ALT+F8, and select the CombineFiles macro). Select one or more files you want to consolidate. When prompted to save your file, type in Pliant.xls.
Does this work?
Create a new spreadsheet (let's call it Consolidate.xls. Open the VBA Editor (ALT+F11). In the Project Explorer window (usually top left window), select the VBAProject names Consolidate.xls. From the menu bar at the top, select Insert|Module. Paste my code in the VBA window (usually the window on the right). Close the VBA Editor (ALT+Q). Save Consolidate.xls. Run the macro (ALT+F8, and select the CombineFiles macro). Select one or more files you want to consolidate. When prompted to save your file, type in Pliant.xls.
Does this work?
Upvote
0
Similar threads
