Saving a file with a variable pathname and filename

[Tarver]

For some unknown (to me!) reason, I can't get this to work. The debugger definitely doesn't like my code, but I don't know why. Anyone have any ideas?

Sub ReportDaily()

'Set up Variables
    Dim FName   As String
    Dim FPath   As String
    Dim NewBook    As Workbook

    FPath = "X:\Sales\2020 Daily Sales Report\E-Mailed Reports"
    FName = "2020 Daily Sales through " & Format(Now(), "MM-DD") & ".xlsx"

'Do some things

'Save the file
    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists."
    Else
        MsgBox "File to be saved as " & FPath & "\" & FName
        Workbook.SaveAs FileName:=FPath & "\" & FName
    End If
    
End Sub

I get a Run-time error '424', Object required when the code executes.

Any advice on how to correct this would be most appreciated.

 

[Fluff]

Are you trying to save the workbook that contains the code?

 

[Tarver]

Are you trying to save the workbook that contains the code?

Yes, I am.

 

[Fluff]

In that case it should be

ThisWorkbook.SaveAs FileName:=FPath & "\" & FName

 

[Tarver]

Well, wait. Correct my hasty reply.

I'm trying to save two workbooks, a new one that has no name (that should be defined by my code) and the original workbook that created the new workbook.

 

[Fluff]

The code you posted is not creating a new workbook.

 

[Tarver]

You're correct. That code is in the 'Do Some Stuff comment section I posted. I left that out because it was long and unnecessary to share, I thought.

The code copies several tabs into a new file. That new file is active when my save code runs. My code runs all the way through to the last line,

Workbook.SaveAs FileName:=FPath & "\" & FName

where it then errors out.


I appreciate you trying to help me. Sorry that I'm making it confusing.

 

[Fluff]

In that case use ActiveWorkbook, instead of Workbook

 

[Tarver]

Excellent! That works. Thanks very much!

 

[Fluff]

You're welcome & thanks for the feedback.