saving single worksheet

[E.J. van Wijngaarden]

Hello,

I want to save one worksheet of a workbook as a separate xls-file.
I use: Worksheets("test").SaveAs "test1.xls".

According to the help this should do the job.
But everytime the whole workbook is saved, including all other worksheets.
How to solve this?

Thanks for your answer.

Ed van Wijngaarden

 

[Ron de Bruin]

Hi E.J.

' copy the sheet to a new workbook
Worksheets("test").Copy
' save the worbook with only the sheet test
ActiveWorkbook.SaveAs "C:\test1.xls"

 

[Paulw2k]

Hi,

This will do it.


Sub SaveTestSheetSeparately()
Worksheets("test").Copy
ActiveWorkbook.SaveAs "test1.xls"
End Sub

Paul

 

[E.J. van Wijngaarden]

Thanks for both answers.

I changed the code to:

Sub SaveTestSheetSeparately()
Application.ScreenUpdating = False
Worksheets("test").Copy
ActiveWorkbook.SaveAs "test1.xls"
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

Now it does exactly what I want.

Ed