Save only one worksheet from a file

syed_mushraf

Active Member
Joined
Oct 13, 2002
Messages
259
How can I save only one worksheet separately from a file which conatains 10 worsheet.
Rgds,
Musharraf
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub mySaveSheet()
Dim mySheet
Dim myFile
'The current Workbook.
myFile = ActiveWorkbook.Name
'The current Work-Sheet.
mySheet = ActiveSheet.Name
Sheets(mySheet).Select
'Make a copy of the current Work-Sheet.
Sheets(mySheet).Copy
'Save this Work-Sheet using the Excel "Save-As" dialog screen.
Application.Dialogs(xlDialogSaveAs).Show
'Keep the new file open!
Application.Workbooks(myFile).Activate

End Sub

Hope this helps, it only saves the current sheet in a new file, that you get to place and name. JSW
 

syed_mushraf

Active Member
Joined
Oct 13, 2002
Messages
259
On 2002-10-15 23:58, Joe Was wrote:
Sub mySaveSheet()
Dim mySheet
Dim myFile
'The current Workbook.
myFile = ActiveWorkbook.Name
'The current Work-Sheet.
mySheet = ActiveSheet.Name
Sheets(mySheet).Select
'Make a copy of the current Work-Sheet.
Sheets(mySheet).Copy
'Save this Work-Sheet using the Excel "Save-As" dialog screen.
Application.Dialogs(xlDialogSaveAs).Show
'Keep the new file open!
Application.Workbooks(myFile).Activate

End Sub

Hope this helps, it only saves the current sheet in a new file, that you get to place and name. JSW
Where should I write this code.
 

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
Try the following which does not involve any VBA code

Select the sheet you wish to save separately.
Right click on the sheet tab to bring up the shortcut menu.
Select "Move or Copy ..."
In the "To Book" dropdown box, select "(new book)".
If you want to COPY the selected sheet and leave the original in the source workbook, tick the "Create a copy" checkbox. If you leave this checkbox blank, Excel will move (cut) the sheet from the source workbook.
Click the Ok button.
Hey presto, you now have the selected sheet in a new workbook which you can now save (Save as) in a separate file.

Ole, no VBA!!

HTH
BigC
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Right click the Sheet-Tab lable for the sheet that you want to save as a workbook. Then select "View Code" then paste a copy of the posted code to the sheet module displayed. Close with the upper close "X" and from the Excel menu select Tools-Macros-Macro and run the code or build a form button and attach the macro code to the button. JSW
 

chadom

New Member
Joined
Aug 25, 2015
Messages
11
Sub mySaveSheet()
Dim mySheet
Dim myFile
'The current Workbook.
myFile = ActiveWorkbook.Name
'The current Work-Sheet.
mySheet = ActiveSheet.Name
Sheets(mySheet).Select
'Make a copy of the current Work-Sheet.
Sheets(mySheet).Copy
'Save this Work-Sheet using the Excel "Save-As" dialog screen.
Application.Dialogs(xlDialogSaveAs).Show
'Keep the new file open!
Application.Workbooks(myFile).Activate

End Sub

Hope this helps, it only saves the current sheet in a new file, that you get to place and name. JSW


Any way to save 2 worksheets from the workbook using a variation of this code above?
Thank you!
 

Forum statistics

Threads
1,144,363
Messages
5,723,914
Members
422,527
Latest member
JayTheKaz

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
Top