I am using excel VBA in office 97.
I have a multiple sheet workbook ,and just need to save a single sheet to a file.
The only way I can seem to do it is as follows
Sheets("test1").Select 'select the sheet
Sheets("test1").Copy 'this opens a new workbook and places the selected sheet into it.
ActiveWorkbook.SaveAs FileName:="C:\Book2.xls" 'save this new workbook
ActiveWindow.Close 'close the workbook, and return to the original workbook
This however creates a new workbook, with the selected sheet in it, then saves this workbook and then closes, thus returning me to my original workbook. It works, but involves lots of screen flashing and is time consuming.
What I want to do is just somehow "save the selected worksheet to a file", without having to copy it to a new workbook first. (i do not want to lose any formatting, so it needs to be saved as an .xls)
any help would be appreciated.