Save just one sheet in a workbook

G

Guest

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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Because how long do you think a macro would take to do it , repetive tasks are best undertaken by code.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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??
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top