Macro not working.

[Robert Wyatt]

I have a macro that has stopped working correctly and I'm at my wits end. It says it has either been moved, no longer exits or another program is using it. But I have only one program using this Macro. any help would be greatly appreciated. It does the printing, and posting, but not saving.

Sub SavePayrollKeeper2024WithNewName()
Dim NewFN As Variant
PostToYTD
PostToSocialSecurityRegister
PostToPTORegister


' Copy Next Pay Period to a new workbook
ThisWorkbook.Sheets.Copy
NewFN = "D:\Payroll Keeper 2024\Earning Statements\ Pay Period" & " # " & Range("B6").Value & " - " & Format(Range("B7"), "mm-dd-yyyy") & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled (it's this part that keeps showing up in yellow}
ActiveWorkbook.Close
End Sub

 

[rollis13]

Well, your pathname\filename has no syntax error (except that your cells K2, K3 are empty) so there is no reason it won't save.
That said, the only other thought is that cells K2 and K3 contain invisible, unusable characters in a filename.

 

[Robert Wyatt]

Well, your pathname\filename has no syntax error (except that your cells K2, K3 are empty) so there is no reason it won't save.
That said, the only other thought is that cells K2 and K3 contain invisible, unusable characters in a filename.

I have it to where it is working, but It's only saving the time sheet it's not saving the Earning Statements. I copied the code that was sub jested and now it's only saving one worksheet not two. This is the code I was told to use, It works, but saves only time sheet I need it to save everyting i have in the workbook.
Dim NewFN As String
NewFN = "C:\Folder Name Here\Temp" & Range("K2").Value & " # " & Range("K3").Value & ".xlsm"
ActiveSheet.Copy '<---- I used sheet here
ActiveWorkbook.SaveAs NewFn, 52
ActiveWorkbook.Close
End Sub

 

[Alex Blakenburg]

Just remove this line:
ActiveSheet.Copy '<---- I used sheet here

 

[Robert Wyatt]

Just remove this line:
ActiveSheet.Copy '<---- I used sheet here

I removed the line you said to remove and now its showing error on the ActiveWorkBook.SaveAs New Fn, 52. showing up in yellow.

 

[Robert Wyatt]

Re: "Activeworkbook.saveAs NewFn 52 in yellow."
It should as you did not change it to reflect your path.
I doubt very much that this is a valid path on your computer.

NewFN = "C:\Folder Name Here\Temp" & Range("K2").Value & " # " & Range("K3").Value & ".xlsm"

This is the valid path on my computer

NewFN = "D:\Blank Payroll Keeper\Earning Statements\Pay Period" & Range("B6").Value & " # " & Range("B7").Value & ".xlsm"

 

[Skyybot]

I didn't think that number signs could be used in Windows filenames...

 

[Robert Wyatt]

I didn't think that number signs could be used in Windows filenames...

Been using it for the last 15 years. It just stopped working two days ago. Up until then I would Print, Post and save without a problem.

 

[rollis13]

So let's go back to your post #1 and change it to this (note: you may need to fix the NewFN line to reference to the right sheet):

Option Explicit
Sub SavePayrollKeeper2024WithNewName()
    Dim NewFN  As String                          '<- changed
    PostToYTD
    PostToSocialSecurityRegister
    PostToPTORegister
    ' copy Next Pay Period to a new workbook
    ThisWorkbook.Sheets.Copy
    NewFN = "D:\Payroll Keeper 2024\Earning Statements\Pay Period" & " # " & Range("K2").Value & " - " & Format(Range("K3"), "mm-dd-yyyy") & ".xlsm" '<- adjust as needed
    'NewFN = "D:\Payroll Keeper 2024\Earning Statements\Pay Period" & " # " & Sheets("xxxxx").Range("K2").Value & " - " & Format(Sheets("xxxxx").Range("K3"), "mm-dd-yyyy") & ".xlsm" '<- adjust as needed
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close
End Sub

 

[Robert Wyatt]

So let's go back to your post #1 and change it to this (note: you may need to fix the NewFN line to reference to the right sheet):

Option Explicit
Sub SavePayrollKeeper2024WithNewName()
    Dim NewFN  As String                          '<- changed
    PostToYTD
    PostToSocialSecurityRegister
    PostToPTORegister
    ' copy Next Pay Period to a new workbook
    ThisWorkbook.Sheets.Copy
    NewFN = "D:\Payroll Keeper 2024\Earning Statements\Pay Period" & " # " & Range("K2").Value & " - " & Format(Range("K3"), "mm-dd-yyyy") & ".xlsm" '<- adjust as needed
    'NewFN = "D:\Payroll Keeper 2024\Earning Statements\Pay Period" & " # " & Sheets("xxxxx").Range("K2").Value & " - " & Format(Sheets("xxxxx").Range("K3"), "mm-dd-yyyy") & ".xlsm" '<- adjust as needed
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close
End Sub

I did just as you said to do, but it still showing ActiveWorkbook.SaveAs NewFN FileFormat:=xlOpenXMLWorkbookMacroEnabled error in yellow. I know for a fact that path is correct and I don't have any file open with the same name. When it show up in the Immadiate is shows Files name and Pay Period number as a date and It should be just 1 single number and then the date after.

 

[rollis13]

Well, now that we're 20 post away and haven't gotten anywhere yet, do you mind attaching a working file with a few lines of non-sensitive data.