Save a single sheet

[kubek]

Hello, i was trying to set the code tahat saves a single shhet (active one or other...) as a excel file. This code however saves all the file so all the sheets why? i have put the restriction: save single sheet and i have done the clue sheet1. But it doesnt play as it shoud.. Any advice?


[VBA]
Sub SaveSingleSheet()
Dim file_name As Variant
' Get the file name.
file_name = Application.GetSaveAsFilename( _
FileFilter:="Excel Files,*.xls,All Files,*.*", _
Title:="Save As File Name")
' See if the user canceled.
If file_name = False Then Exit Sub
' Save the file with the new name.
If LCase$(Right$(file_name, 4)) <> ".xls" Then
file_name = file_name & ".xls"
End If
Worksheets("Sheet1").SaveAs Filename:=file_name
End Sub
[/VBA]

[Paul_Hossler]

XL can only save workbooks, so you need to put the sheet to be saved into it's own new WB, and save that

Here's some cleaned up code fro the macro recorder

[VBA]
Sub Macro1()
Dim wb As Workbook

Set wb = ActiveWorkbook
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="MyFile", FileFormat:=xlNormal
ActiveWindow.Close
wb.Activate
End Sub
[/VBA]

Paul

[kubek]

ok thanks - i have found the code that matches exactly what i was looking for:

[VBA]
Sub Macro1()

Dim strFName As Variant

Sheets("DATA").Copy

strFName = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xls), *.xls")
'The following If-Then sequence can be removed, but it's there for informational purposes
If strFName = False Then
MsgBox "You hit Cancel"
Else
MsgBox strFName
End If

ActiveWorkbook.SaveAs Filename:=strFName

End Sub
[/VBA]
problem is when i click cancel on the saving window it opens the new sheet and doesnt close which is annoing - how to fix it any ideas?

[mdmackillop]

Hi Kubek,
Please use the green VBA button to format your code.
Regards
MD

[mdmackillop]

[VBA]Sub Macro1()
Dim strFName As Variant
strFName = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xls), *.xls")
'The following If-Then sequence can be removed, but it's there for informational purposes
If strFName = False Then
MsgBox "You hit Cancel"
Else
Sheets("DATA").Copy
ActiveWorkbook.SaveAs Filename:=strFName
End If
End Sub
[/VBA]