Solved: Save sheet to new workbook

[Riaaz66]

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?

Sub Sheet_SaveAs()
Sheets("Output").Select
ActiveSheet.SaveAs "C:\Test (file://\\solon.prd\files\P\Global\Users\n00406\UserData\Documents\Project) - Recon_Output_ " & Format(Date, "yyyymmdd")
End Sub

Regards,

RM

[GTO]

Greetings RM,

See if this helps:


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

[Riaaz66]

Hi GTO,

It worked. Thanks a lot.

Regards,

RM

[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.
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 With SaveAs Dialog Browsing
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

[GTO]

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

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