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]

Just a guess, since you get that highlighted line it's very likely that this folder doesn't exist:
D:\Payroll Keeper 2024\Earning Statements\

 

[6StringJazzer]

What is the full value of variable NewFN when this error occurs?

It doesn't matter what else is using the macro, it thinks the problem is with the file. Is is possible you already have a file open in Excel with that name? Are you sure the folder exists?

This not causing your problem but declare NewFN as String instead of Variant.

 

[jolivanes]

If you want to save the whole workbook (ThisWorkbook.Sheets.Copy), would it not be easier to save the workbook with a new name?

I don't have your workbook but this works here. Changed some naming.

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

 

[Robert Wyatt]

What is the full value of variable NewFN when this error occurs?

It doesn't matter what else is using the macro, it thinks the problem is with the file. Is is possible you already have a file open in Excel with that name? Are you sure the folder exists?

This not causing your problem but declare NewFN as String instead of Variant.

Yes, the folder does exist and no I do not already have a file open in excel with that name.

 

[Robert Wyatt]

If you want to save the whole workbook (ThisWorkbook.Sheets.Copy), would it not be easier to save the workbook with a new name?

I don't have your workbook but this works here. Changed some naming.

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

I tried using your code, now it shows Activeworkbook.saveAs NewFn 52 in yellow.

 

[Alex Blakenburg]

A space before "Pay Period" in the file name seems unlikely is that really correct ?
You can also try putting the line below before your save as:
debug.print NewFN
Then copy the value from the immediate window and try a manual Save As on the file pasting what was in the immediate window in the save box and see what you need to change to make it work.

 

[rollis13]

Stop the macro and just before the highlighted line add this other:

Debug.Print NewFN

then use Ctrl+G to show the Immediate pane (if not already visible), then relaunch the macro and when the macro stops again post what you read in the Immediate pane.

 

[jolivanes]

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"

 

[Robert Wyatt]

Stop the macro and just before the highlighted line add this other:

Debug.Print NewFN

then use Ctrl+G to show the Immediate pane (if not already visible), then relaunch the macro and when the macro stops again post what you read in the Immediate pane.

This is what I ready in the immediate pane.
D:\Time Sheet\Earning Statements\Temp # .xlsm