Saving in a new working book VBA

[Minato5972]

Sub Saving(ws As Worksheet)


Dim dt As String
Dim wbNam As String
Dim sFileSaveName As Variant

wbNam = "Salesforecast_"
dt = Format(CStr(Now), "dd_mm_yyy_hh_mm_ss")



sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=wbNam & dt, fileFilter:="Excel Files (*.xlsm), *.xlsm")

If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If


End Sub


Above code is doing the job but i want only one particular sheet named "Forecast" from it, but it is saving all the sheets present in my current workbook

 

[Anthony47]

...but i want only one particular sheet named "Forecast" from it...

The subroutine is passed an argument "as Worksheet"; I guess this argument points to the sheet you are willing to copy.
In this case, you just need adding this instruction in this position:

Dim sFileSaveName As Variant
ws.Copy                        '<<< ADD THIS HERE
wbNam = "Salesforecast_"

Bye

 

[Minato5972]

I am getting "400" error if use .copy or .move on 'ws'

 

[Anthony47]

The subroutine is passed an argument "as Worksheet"; I guess this argument points to the sheet you are willing to copy.

So my guessing was wrong...
How do you call the subroutine? Wich argumento do you pass to it?

 

[Minato5972]

T

So my guessing was wrong...
How do you call the subroutine? Wich argumento do you pass to it?

The worsksheet which i want to copy/move itself you guessed it correct

 

[Anthony47]

No, my guessing was wrong because the proposed solution didn't work.
How do you call the subroutine? Wich argument do you pass to it?

 

[Minato5972]

I pass the woksheet

Dim wssrc, wsdst As Worksheet
Set wsdst = Worksheets.Add
wsdst.Name = "Forecast"
.
.
.
Call Saving(wsdst)
.
.
.

Sub Saving(ws As Worksheet)

 

[Anthony47]

With that sequence, I have no error when excecuting the Sub Saving modified as per my message #2
Its code:

Sub Saving(ws As Worksheet)
Dim dt As String
Dim wbNam As String
Dim sFileSaveName As Variant
'
ws.Copy
wbNam = "Salesforecast_"
dt = Format(CStr(Now), "dd_mm_yyy_hh_mm_ss")
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=wbNam & dt, fileFilter:="Excel Files (*.xlsm), *.xlsm")
If sFileSaveName <> False Then
    ActiveWorkbook.SaveAs sFileSaveName
End If
End Sub

Add these 3 debug lines just before the line "ws.copy":

Dim sFileSaveName As Variant
'
'>>> ADD THE NEXT 3 LINES:
Debug.Print TypeName(ws)
Debug.Print ws.Name
Debug.Print ws.Parent.Name
ws.Copy

Then when you get the error, enter the debug mode; check and tell us wich is the line in error. Then open the vba "Immediate window" (typing Contr-g should do the job; or: menu /View /Immediate window); check that the last 3 lines typed in that window says:

Worksheet
Forecast
TheNameOfTheParentWorkbook

Bye

 

[Minato5972]

Worksheet
Forecast
Sales Forecast_Filter_V2_New Workbook.xlsm

These are the last three lines in immediate window

Getting 1004 when code runs "ActiveWorkbook.SaveAs sFileSaveName" Line

 

[Anthony47]

I don't use your XL version, guess that the problem might be with the extension.
Indeed your new created workbook doesn't include macros, so it should be a .xlsx type
Also, not sure that the workbook is "active" when the dialog box closes.

So I suggest this new code for Sub Saving:

Sub Saving(ws As Worksheet)
Dim dt As String
Dim wbNam As String
Dim sFileSaveName As Variant, nWB As Workbook
'
ws.Copy
Set nWB = ActiveWorkbook
wbNam = "Salesforecast_"
dt = Format(CStr(Now), "dd_mm_yyy_hh_mm_ss")
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=wbNam & dt, fileFilter:="Excel Files (*.xlsx), *.xlsx")
If sFileSaveName <> False Then
    nWB.SaveAs sFileSaveName
End If
End Sub

Try...