Saving Worksheet to new file by tab name
 

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

Saving Worksheet to new file by tab name
 

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

Saving Worksheet to new file by tab name
 

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

Saving Worksheet to new file by tab name
 

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

Saving Worksheet to new file by tab name
 

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

Saving Worksheet to new file by tab name
 

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

Saving Worksheet to new file by tab name
 

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

Saving Worksheet to new file by tab name
 

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