Hi tech2,
I can understand frustration if things don't work, I have been there.
First have you got excel 2007? if you have this should work as it works fine in mine.
Lets run through what needs to be done.
1. Open workbook Report1 (make it the only workbook open)
2. If you now go to developer menu, click on it, now click on Visual Basic icon on the far left. when that is open it should show you module icons, Now double clicking on those icons will bring the macro to the front associated with the icon. One of those icons should have the Macro exactly as I posted on April the 5th which has 2 Subs in it.
If this is not the case you need to copy and paste the complete 2 sub macros as per post ~ April 5th into a new module, click insert from the VBA menu, click Module. Paste the macro.
Have you changed the code in red writing as I instructed? If you haven't do that.
Now open Report 2, and if you put the above macro in that workbook, delete it.
Now to test while both workbooks are open, select workbook Report1, go to the module that the macro is in. Anywhere after the code line ~
Sub GenerateReport2()
Click so that the insertion indicator is blinking. Now click the little green triangle icon so that just that sub runs.
If that does not give you the answers you require, step through that sub by doing the same insertion icon blinking as per previous paragraph. To step through, go to the View menu, click on toolbars, click debug, (It may be already open, if it has a tick next to it it is open) now on the right of the 'hand' icon is your step through icon, by clicking on that it takes you to the first line of code, click again goes to second line of code etc...
If there is a problem in the code it will tell you by highlighting the offending code.
If that happens let me know.
If all going well, now run the macro ~
Sub GenerateBook()
Your compile error may have come up because you did not change the red line of code to your path to workbook Report2. The path currently in the red line is my path that I used to test that the macro worked properly.
Good luck.