Saving Worksheet to new file by tab name

[Bobzter100]

Hi
I need to save a single worksheet from a master workbook by its tab name to
a specific folder on my C: drive. The macro needs to extract the worksheet,
save it to the c: drive, close the new file and return me to the original
file (the master). Also –can I remove the standard Sheet 1, Sheet 2 and Sheet
3 when creating the new file as they are not required.
I have already created the tab name and the folder on the c: drive
(C:\Report Logs)and only need the macro to accomplish the above.
Cheers
Bobzter

 

[Gary''s Student]

Hi Bobzter:

This routine uses C:\temp
Change it to suit your needs:

Sub Macro1()
' gsnuxx
wbn = ActiveWorkbook.Name
shn = ActiveSheet.Name

Workbooks.Add
wbnew = ActiveWorkbook.Name

Windows(wbn).Activate
Sheets(shn).Copy Before:=Workbooks(wbnew).Sheets(1)
Windows(wbnew).Activate

Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Name <> shn Then
sh.Delete
End If
Next
Application.DisplayAlerts = True

destin = "C:\temp\" & shn & ".xls"
ActiveWorkbook.SaveAs Filename:=destin
ActiveWorkbook.Close
End Sub

 

[Joel]

Sub savesheets()
folder = "C:\Report Logs\"

For Each sht In ThisWorkbook.Sheets
If (sht.Name <> "Sheet1") And _
(sht.Name <> "Sheet2") And _
(sht.Name <> "Sheet3") Then

sht.Copy
ActiveWorkbook.SaveAs Filename:= _
folder & sht.Name & ".xls"
ActiveWorkbook.Close
End If
Next sht
End Sub

 

[Bobzter100]

Hi Gary
Thanks for the reply, however..
I get a runtime error 9, "subscript out of range" when I run this macro.
Any idea how to overcome this?
Cheers
Bobzter

 

[Bobzter100]

Hi Joel
Macro runs but copies all worksheets into new files. How do I modify your
code to only save the current active file, i.e. the worksheet that the macro
is run from?
Cheers

 

[Dave Peterson]

You can copy the worksheet to a new workbook
save that new workbook
close that new workbook
and not worry about deleting or going back to the original.

Option Explicit
sub testme()
thisworkbook.worksheets("masterwksnamehere").copy 'to a new workbook
with activesheet 'the new sheet in the new workbook
.parent.saveas _
filename:="C:\report logs\whateverfilename.xls", _
fileformat:=xlworkbooknormal
.parent.close savechanges:=false
end with
end sub

(untested, but it did compile.)

 

[Joel]

This is s simplified version of my other code which saves the selected sheet.
I mis-read you previous posting. Most people want to save all the sheets.

Sub savesheets()
folder = "C:\Report Logs\"
Activesheet.Copy
ActiveWorkbook.SaveAs Filename:= _
folder & sht.Name & ".xls"
ActiveWorkbook.Close
This workbook.activate
End Sub

 

[Irfan Khan]

If u need something very simple than ... Download ASAP utilities, install and
go to Menu>ASAP Utilities>Export>Export Worksheets as separate files...