MrExcel.com - Your One Stop for Excel Tips & Solutions

Export Sheet to new workbook?

Posted by CJ on June 08, 2001 10:40 AM

I have VBA code in Excel that imports an access query into Excel, performs some calculations on it, sorts it, groups it and adds subtotals. Is there a way to export this new data into a new excel file, without including all the code and comand buttons? I need to send it to customers. Any help is greatly appreciated.

Posted by Barrie Davidson on June 08, 2001 10:58 AM

Hi CJ, try this code:

Sub Save_Sheet_As_New_Workbook()
' Written by Barrie Davidson
Dim New_file_name
'
New_file_name = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls")
ActiveSheet.Copy
ActiveWorkbook.SaveAs FileName:=New_file_name, FileFormat:=xlNormal
ActiveWindow.Close
End Sub

Regards,
Barrie

Posted by CJ on June 08, 2001 11:04 AM

Thanks Barrie, but is there a way to export just the data and the subtotals, without any of the macros or code or command buttons? Thanks so much!

Posted by Barrie Davidson on June 08, 2001 11:10 AM

CJ, do you have buttons on the sheet you want to copy?

Posted by CJ on June 08, 2001 11:15 AM

No - I have buttons on the sheet to run the code. Once the code is run, all I want in the new spreadsheet is the newly formatted data. I don't want to macros and the buttons. This will be going to customers and they will get confused if all of that is included as well. Does that make sense?

Posted by Barrie Davidson on June 08, 2001 11:29 AM

Makes perfect sense. All you need to do is change:
ActiveSheet.Copy
to:
Sheets("Name").Copy
where "Name" is the name of the worksheet you want to copy.

Barrie No - I have buttons on the sheet to run the code. Once the code is run, all I want in the new spreadsheet is the newly formatted data. I don't want to macros and the buttons. This will be going to customers and they will get confused if all of that is included as well. Does that make sense?