Move sheets from one Existing spreadsheet into Blank one

[sirrahhc]

Hey guys, thx in advance for any knowledge you share. And sorry in advance if I confuse anyone with my ramblings. Here is what I am trying to accomplish. I have an excel report that is saved to a file share once a day via SSRS (lets call it Report.xls). This file is overwritten each time the report runs so any macro's that are contained in that spreadsheet will be overwritten. This spreadsheet has numerous tabs that I run a macro against to rename each sheet according to a specific cell. I then run another macro that saves off each sheet as it's on workbook and then a VBS file e-mails each new workbook to individuals. My problem lies in the fact that I cannot save these macros in the work sheet Report.xls because it will be overwritten. So Ideally I would have a VBS file (I do but it doesn't work) that opens Report.xls and runs the 2 macros from the Personal Workbook but for some reason when the vbs file opens Report.xls, my personal workbook macros do not show up. If I open Report.xls manually they show up and can run the macros no problem. Does anyone know how to get them to show up when opening an excel file via a vbs file?? If not then my alternative is to create a macro in a blank excel file (lets call it "Template.xls") that I save the macros in. I would then need a macro that does the following:

Copies data into a BLANK excel document (The blank doc IS Template.xls) and then saves off as a new document. Where I'm having trouble is writing a macro that opens an existing workbook and copies all sheets and all data OUT of it and into the BLANK excel workbook.

I would rather just fix the issue with the Personal Macro Workbook not showing up when I fire off my vbs but will take the alternative listed above. I've found plenty of macros to copy data OUT of a workbook with data and into a new blank one but none for copying data INTO a blank document from another document with data. Can anyone help me with either solution?? Thanks guys!!

[timbo1957]

Why not copy and paste the macros into the Personal.xls workbook and save it. Then they will always be there?

[sirrahhc]

I have done exactly that and it works perfectly when calling the macros from the Excel file itself, the "Report.xls" spreadsheet. But when I open that excel file from a VBS file via code and then attempt to call the macros, the "Personal" macros do not show up in "Report.xls" and cannot be called. Only if I open the Excel file manually do they appear. This is ultimately my main problem but I have been googling for over a day now trying to resolve it but to no avail so I was going to try a different route which was to open the "Report.xls" into a static "Template" so to say, that already had the macros present. BTW, the reason I am using a VBS to open the excel file is to completely automate this process from the report running to e-mailing out the individualy Excel workbooks.

[sirrahhc]

Ok, so I ended up finding some stuff about the PERSONAL.XLSB file and getting it to open via the VBS file. So this turned out to be more of a VBS question rather than a Macro question. Anyways, the resolution was to add the following line of code to my VBS file right before the oExcel.Run command:

Set objWorkbook=oExcel.Workbooks.Open("C:\Documents and Settings\<USERNAME>\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB")

This ensured that the Personal workbook was open and macros were available before trying to run the macros. It works perfectly now and I can move on!! Thanks!!