Solved: Save sheet to new workbook

Hi,

I've a workbook with a few sheets. I want to save one particular sheet to a new workbook with a predefined workbookname that also ends with a date. The code below works fine partly, because instead of saving the particular sheet, it save the whole workbook again with the pre-defined workbookname. What is wrong in my code?

[vba]Sub Sheet_SaveAs()
Sheets("Output").Select
ActiveSheet.SaveAs "C:\Test - Recon_Output_ " & Format(Date, "yyyymmdd")
End Sub[/vba]

Regards,

RM

Greetings RM,

See if this helps:

Code:

---

Sub Sheet_SaveAs()
Dim wb As Workbook
   
    '// Set as reference to a new, one-sheet workbook.                              //
    Set wb = Workbooks.Add(xlWBATWorksheet)
    With wb
        '// Skip selecting the sheet, just reference it explicitly and copy it after//
        '// the blank sheet in the new wb.                                          //
        ThisWorkbook.Worksheets("Output").Copy After:=.Worksheets(.Worksheets.Count)
        '// Kill alerts, delete the blank sheet in the new wb and turn alerts back on//
        Application.DisplayAlerts = False
        .Worksheets(1).Delete
        Application.DisplayAlerts = True
        '// SaveAs the new workbook to whatever path and close.                    //
        .SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
        .Close False
    End With
End Sub

---

Mark

Hi GTO,

It worked. Thanks a lot.

Regards,

RM

Hi GTO,

The two versions of code below have been working for me. No new sheet and delete required.

I highly respect your abilities (as far above my own), and am only posting these for the purpose of sharing and learning. I am happy to have any parts of it that may be faulty pointed out to me.
[vba]Sub Sheet_SaveAs()

Dim wb As Workbook

Sheets("Output").Copy

Set wb = ActiveWorkbook

With wb
.SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
.Close False
End With

End Sub[/vba] With SaveAs Dialog Browsing
[vba]Sub Sheet_SaveAs_Dialog()

Dim wb As Workbook, InitFileName As String, fileSaveName As String

InitFileName = ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")

Sheets("Output").Copy
' or below for more than one sheet
' Sheets(Array("Output", "Sheet2", "Sheet3")).Copy

Set wb = ActiveWorkbook

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
filefilter:="Excel files , *.xlsx")

With wb
If fileSaveName <> "False" Then

.SaveAs fileSaveName
.Close
Else
.Close False
Exit Sub
End If
End With

End Sub[/vba]

Quote:

---

Originally Posted by frank_m

Hi GTO,

The two versions of code below have been working for me. No new sheet and delete required.

I highly respect your abilities (as far above my own), and am only posting these for the purpose of sharing and learning. I am happy to have any parts of it that may be faulty pointed out to me.
[vba]Sub Sheet_SaveAs()

Dim wb As Workbook

Sheets("Output").Copy

Set wb = ActiveWorkbook

With wb
.SaveAs ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
.Close False
End With

End Sub[/vba]
...

---

Greetings Frank,

Thank you for the compliment; as a 'laymen' (at best somedays) coder, I learn something new each day, or at least every chance I get to spend some time here.

For what it's worth, IMO, there is certainly nothing wrong and no real disadvantage to the method you used. I have used that way myself, but am simply in the habit of setting a reference to the object wb at creation, rather than grabbing the active wb. Again, really just a preference on my part.

Mark