export and save data without leaving current workbook

[adambensaid]

Hello VBA crusaders,
I have a workbook with lots of calculations grouped by categories. I have a userform that directs users to initiate calculations in specific categories. Currently, the workbook parses the data and saves itself as a .xls. Instead, I would like to take just the sheets related to the chosen category and export and save them to a new .xls file without leaving the current workbook. At the end of all the analyses, the user would be able to save the "master sheet" but will also have a few saved spreadsheets representing reports from the different categories.
Thank you in advance for the help.
Adam

Excel 2007
Windows XP SP2

 

[Ruddles]

Perhaps .SaveCopyAs might be the way to go. Check http://msdn.microsoft.com/en-us/library/bb178003(v=office.12).aspx.

(This saves the entire workbook so you may need to re-open the copy and delete sheets you don't want people to see.)

 

[adambensaid]

Perhaps .SaveCopyAs might be the way to go. Check http://msdn.microsoft.com/en-us/library/bb178003(v=office.12).aspx.

(This saves the entire workbook so you may need to re-open the copy and delete sheets you don't want people to see.)

Thank you Ruddles. Unfortunately, your suggestion gives me a way to save a copy of the entire workbook, which defeats the purpose of having a category-specific report. If I don't find a better way, I may just have to let VBA open the saved books and remove the unneeded content, which would add significant processing time.
Any more convenient suggestions?

 

[Ruddles]

No, it's horrible but I couldn't think of any other way. Soz!

 

[adambensaid]

All,
I still can't find a solution to this issue. I have to call on the collective wisdom of the VBA community for an answer.