Saving in a new working book VBA

Minato5972

New Member
Joined
Mar 18, 2021
Messages
11
Office Version
  1. 2019
  2. 2016
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
...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:
VBA Code:
Dim sFileSaveName As Variant
ws.Copy                        '<<< ADD THIS HERE
wbNam = "Salesforecast_"

Bye
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
I pass the woksheet

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

Sub Saving(ws As Worksheet)
 
Upvote 0
With that sequence, I have no error when excecuting the Sub Saving modified as per my message #2
Its code:
VBA 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":
Code:
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:
Rich (BB code):
Worksheet
Forecast
TheNameOfTheParentWorkbook

Bye
 
Upvote 0
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
 

Attachments

  • Error 1004.PNG
    Error 1004.PNG
    32.9 KB · Views: 8
Upvote 0
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:
VBA Code:
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...
 
Upvote 0
Solution

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top