Save only one worksheet from a file

syed_mushraf

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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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