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

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 Befo=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
--
Gary''s Student - gsnu200755


"Bobzter100" wrote:

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

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" wrote:

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

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

"Gary''s Student" wrote:

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 Befo=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
--
Gary''s Student - gsnu200755


"Bobzter100" wrote:

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

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

"Joel" wrote:

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" wrote:

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

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.)


Bobzter100 wrote:

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

--

Dave Peterson

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


"Bobzter100" wrote:

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

"Joel" wrote:

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" wrote:

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

If u need something very simple than ... Download ASAP utilities, install and
go to MenuASAP UtilitiesExportExport Worksheets as separate files...



"Bobzter100" wrote:

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