Save just one sheet in a workbook

[Guest]

I have a workbook with so many sheets in it. I want to save just one sheet of the workbook, I mean I do not want to save the entire workbook.

Thanks,

Ayhan KIZILTAN

 

[RWinfield]

Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.SaveAs Filename:="YOUR DIR" & YOUR FILENAME & ".xls"
Application.ScreenUpdating = True
Exit Sub

This will copy the active sheet to a new workbook with a single sheet then save it.

Rick

 

[tanayhan]

On 2002-02-21 08:17, RWinfield wrote:
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.SaveAs Filename:="YOUR DIR" & YOUR FILENAME & ".xls"
Application.ScreenUpdating = True
Exit Sub

This will copy the active sheet to a new workbook with a single sheet then save it.

Rick

Thanks Rick,

I am not so good in using vba and macros. Please help me how can I make the code work, which you sendme.

Ayhan

 

[RWinfield]

Sub savesheet2()
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("A1").Value
ActiveSheet.SaveAs Filename:="C:temp" & ThisFile & ".xls"
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub

To use this open the workbook that you want to use this code in, then open the Visual Basic editor (press alt+F11). Once there click the INSERT menu item and choose MODULE from that menu. Copy the above code into the window. You need to make two changes to the above code.

ThisFile = Range("A1").Value...change A1 to an unused cell on your worksheet. This is the cell where you will enter the file name that you want the sheet saved as.

ActiveSheet.SaveAs Filename:="C:temp" ... change to the directory you want the file saved to.

Once you have done this close the VB editor and the macro will appear under TOOLSMACROS menu on the workbook. If you want the macro to be called something other than savesheet2 then edit the name prior to closing the VB editor.

Enter a file name in the cell you chose and run the macro. The macro will copy the active sheet to a new workbook with one sheet, then save the workbook as the filename you input, then will close the new workbook, which returns you to the original workbook with multiple sheets that you were working on.

Hope this make some sense

Rick

 

[Guest]

This may seem silly, but why not simply use the Edit / Move or Copy Sheet feature? Tick the "Copy" box, put it in a new workbook and voila, one sheet saved.

Nanette

 

[brettvba]

Because how long do you think a macro would take to do it , repetive tasks are best undertaken by code.

 

[RWinfield]

On 2002-02-21 12:05, Anonymous wrote:
This may seem silly, but why not simply use the Edit / Move or Copy Sheet feature? Tick the "Copy" box, put it in a new workbook and voila, one sheet saved.

Nanette

The reason in the instance that I wrote the macro for was to use in a program that prints packing lists out. I have around 15 workbooks with about 200 sheets for different products and options, and this macro is actually part of another that basically prints the packing list, then saves an electronic copy of it. It reduces the file save sizes by about a factor of 10 to just save a single sheet instead of the entire workbook. The macro makes sense if you are running this particular series of workbooks on the average of 50 times a day.

The other advantage to using a macro is that you can build in automatic error checking routines to stop duplicate file names, save a file to a particular directory without having to sort through a myriad of network folders and hopefully prevent most of the human errors that will occur with doing this process manually.

I am assuming that the person who requested this information probably has something similar in mind.

In a case where you are saving a single sheet occasionally then you are correct the manual method is much faster.

Rick

 

[tanayhan]

On 2002-02-21 09:08, RWinfield wrote:
Sub savesheet2()
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("A1").Value
ActiveSheet.SaveAs Filename:="C:temp" & ThisFile & ".xls"
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub

To use this open the workbook that you want to use this code in, then open the Visual Basic editor (press alt+F11). Once there click the INSERT menu item and choose MODULE from that menu. Copy the above code into the window. You need to make two changes to the above code.

ThisFile = Range("A1").Value...change A1 to an unused cell on your worksheet. This is the cell where you will enter the file name that you want the sheet saved as.

ActiveSheet.SaveAs Filename:="C:temp" ... change to the directory you want the file saved to.

Once you have done this close the VB editor and the macro will appear under TOOLSMACROS menu on the workbook. If you want the macro to be called something other than savesheet2 then edit the name prior to closing the VB editor.

Enter a file name in the cell you chose and run the macro. The macro will copy the active sheet to a new workbook with one sheet, then save the workbook as the filename you input, then will close the new workbook, which returns you to the original workbook with multiple sheets that you were working on.

Hope this make some sense

Rick

Thanks Rick,

It worked.

Ayhan

 

[r71lima]

How would this code change if you only wanted to copy a Range of information from the active sheet instead of the entire sheet to another file and then have the file save to the Desktop instead of C: drive??