Active WorkBook

[Robert Wyatt]

What it mean with get the error when trying to save a file? I know that my workbook is save in this format.
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Here is the formula I'm using

Sub SaveTimeSheetWithNewName()
    Dim NewFN As Variant
    PostToYTD
    PostToSocialSecurityRegister
   
    ' Copy Next Pay Period to a new workbook
    Dim wbThis As Workbook
    Dim wsThis As Worksheet
    Dim NewFldr As String
   
    Set wbThis = ThisWorkbook
    Set wsThis = wbThis.Worksheets("Time Sheet")
    NewFldr = "D:\Time Sheet\Earnings Statements                ' <--- Copy in the Address from Explorer"
   
    If Right(NewFldr, 1) <> Application.PathSeparator Then NewFldr = NewFldr & Application.PathSeparator
   
    With wsThis
        NewFN = NewFldr & "Pay Period" & " # " & .Range("B13").Value & " - " & Format(.Range("H10"), "mm-dd-yyyy") & ".xlsm"
    End With
   
    wbThis.Sheets.Copy
    Debug.Print NewFN
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close
End Sub

 

[SunnyAlv]

I've tried it, the only problem is that you put the wrong quotes “” in the code below

NewFldr = "D:\Time Sheet\Earnings Statements ' <--- Copy in the Address from Explorer"

when I put it correctly the code runs well, like this

Sub SaveTimeSheetWithNewName()
Dim NewFN As Variant
PostToYTD
PostToSocialSecurityRegister

' Copy Next Pay Period to a new workbook
Dim wbThis As Workbook
Dim wsThis As Worksheet
Dim NewFldr As String

Set wbThis = ThisWorkbook
Set wsThis = wbThis.Worksheets("Time Sheet")
NewFldr = "D:\Time Sheet\Earnings Statements" ' <--- Copy in the Address from Explorer

If Right(NewFldr, 1) <> Application.PathSeparator Then NewFldr = NewFldr & Application.PathSeparator

With wsThis
NewFN = NewFldr & "Pay Period" & " # " & .Range("B13").Value & " - " & Format(.Range("H10"), "mm-dd-yyyy") & ".xlsm"
End With

wbThis.Sheets.Copy
Debug.Print NewFN
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End Sub

 

[Robert Wyatt]

I've tried it, the only problem is that you put the wrong quotes “” in the code below


when I put it correctly the code runs well, like this

Sub SaveTimeSheetWithNewName()
Dim NewFN As Variant
PostToYTD
PostToSocialSecurityRegister

' Copy Next Pay Period to a new workbook
Dim wbThis As Workbook
Dim wsThis As Worksheet
Dim NewFldr As String

Set wbThis = ThisWorkbook
Set wsThis = wbThis.Worksheets("Time Sheet")
NewFldr = "D:\Time Sheet\Earnings Statements" ' <--- Copy in the Address from Explorer

If Right(NewFldr, 1) <> Application.PathSeparator Then NewFldr = NewFldr & Application.PathSeparator

With wsThis
NewFN = NewFldr & "Pay Period" & " # " & .Range("B13").Value & " - " & Format(.Range("H10"), "mm-dd-yyyy") & ".xlsm"
End With

wbThis.Sheets.Copy
Debug.Print NewFN
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close
End Sub

I sure do thank you. I have tried everything I knew to do and didn't even realize I had made the mistake. thank you again for the help.

 

[SunnyAlv]

No Problem

 

[Peter_SSs]

@Robert Wyatt
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

If your code had been in the tags from the start, the error would have been even more obvious to readers because of the colour of the text in that line.