Macro not working.

Robert Wyatt · Jan 6, 2024

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

Robert Wyatt · Jan 7, 2024

This is just the Time Sheet, but you well see where Pay Period number is located and where Pay Period Date is.

Bi-Weekly Time Sheet.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

1

BI-WEEKLY TIME SHEET

2

3

Date Hired

01/06/2024

Employee's Details

4

Beginning

12/15/2023

Employee's Name

Name

401K Percentage Rate

10.00%

5

Ending

12/28/2023

Social Security Number

000-00-0000

Medical Insurance

50.00

6

Pay Period

1

Date Of Birth

Date of Birth

Dental/Vision Insurance

25.00

7

Pay Date

Base Pay/Rate

Total Time Employed

8

Years

Months

Days

9

10

Weekday Date

Options

Time In

Time Out

Time In

Time Out

Worked Hrs.

Regular Hrs.

Overtime Hrs.

PTO Hrs.

Sick Hrs.

Vacation Hrs.

12

Fri 12/15

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

13

Sat 12/16

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

14

Sun 12/17

Off

15

Mon 12/18

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

16

Tue 12/19

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

17

Wed 12/20

Off

18

Thu 12/21

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

19

Total Hrs.

40.00

20

21

Fri 12/22

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

22

Sat 12/23

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

23

Sun 12/24

Off

24

Mon 12/25

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

25

Tue 12/26

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

26

Wed 12/27

Off

27

Thu 12/28

Regular

7:00 AM

11:00 AM

12:00 PM

4:00 PM

8.00

28

Total Hrs.

40.00

29

30

31

32

33

Worked Hrs.

Regular Hrs.

Overtime Hrs.

PTO Hrs.

Sick Hrs.

Vacation Hrs.

34

Total Bi-Weekly Hrs.

80.00

35

36

Base Pay/Rate:

9.92

14.88

9.92

37

Earnings:

793.60

38

39

Total Bi-Weekly Earnings:

$ 793.60

40

41

42

43

Time Sheet

Cell Formulas
Range	Formula
L4	L4	=IF(B4="","",VLOOKUP(G4,Settings!H1:I8,2,0))
L5	L5	=IF(B4="","",VLOOKUP(G5,Settings!H1:I8,2,0))
L6	L6	=IF(B4="","",VLOOKUP(G6,Settings!H1:I8,2,0))
B5	B5	=IF(B4="","",B4+13)
B6	B6	=IF(B4="","",INDEX(Settings!A2:A28,MATCH(B7,Settings!D2:D28,0)))
B7	B7	=IF(B4="","",B4+20)
E4	E4	=IF(B4="","",VLOOKUP(C4,Settings!H1:I8,2,0))
E5	E5	=IF(B4="","",VLOOKUP(C5,Settings!H1:I8,2,0))
E6	E6	=IF(B4="","",VLOOKUP(C6,Settings!H1:I8,2,0))
E7	E7	=IF(B4="","",VLOOKUP(C7,Settings!H1:I8,2,0))
J7	J7	=IF(B3="","",DATEDIF(B3,TODAY(),"Y"))
K7	K7	=IF(B3="","",DATEDIF(B3,TODAY(),"YM"))
L7	L7	=IF(B3="","",DATEDIF(B3,TODAY(),"MD"))
G12:G18,G21:G27	G12	=ROUND(IF((OR(C12="",D12="")),0,IF((D12<C12),((D12-C12)24)+24,(D12-C12)24))+IF((OR(E12="",F12="")),0,IF((F12<E12),((F12-E12)24)+24,(F12-E12)24)),2)
H12:H18,H21:H27	H12	=G12-I12
I12	I12	=ROUND(MAX(IF($V$6,MAX(0,SUM(H11:H$11)+G12-$V$7),0),IF($V$3,IF(G12>$V$4,G12-$V$4,0),0)),2)
I13:I18	I13	=ROUND(MAX(IF($V$6,MAX(0,SUM(H$11:H12)+G13-$V$7),0),IF($V$3,IF(G13>$V$4,G13-$V$4,0),0)),2)
A12	A12	=IF($B$4="","",$B$4)
A13	A13	=IF($B$4="","",$B$4+1)
A14	A14	=IF($B$4="","",$B$4+2)
A15	A15	=IF($B$4="","",$B$4+3)
A16	A16	=IF($B$4="","",$B$4+4)
A17	A17	=IF($B$4="","",$B$4+5)
A18	A18	=IF($B$4="","",$B$4+6)
H19:L19,H28:L28	H19	=SUM(H12:H18)
I21:I27	I21	=ROUND(MAX(IF($V$6,MAX(0,SUM(H$20:H20)+G21-$V$7),0),IF($V$3,IF(G21>$V$4,G21-$V$4,0),0)),2)
A21	A21	=IF($B$4="","",$B$4+7)
A22	A22	=IF($B$4="","",$B$4+8)
A23	A23	=IF($B$4="","",$B$4+9)
A24	A24	=IF($B$4="","",$B$4+10)
A25	A25	=IF($B$4="","",$B$4+11)
A26	A26	=IF($B$4="","",$B$4+12)
A27	A27	=IF($B$4="","",$B$4+13)
I31:L31	I31	=SUM(I28,I19)
G34	G34	=SUM(G12:G27)
H34:L34	H34	=SUM(H28,H19)
H36	H36	=IF(B4="","",E7)
I36	I36	=IF(B4="","",ROUND(1.5*H36,2))
J36	J36	=IF(B4="","",E7)
K36	K36	=IF(B4="","",E7)
L36	L36	=IF(B4="","",E7)
H37:L37	H37	=IF($B$4="","",ROUND(H36*H34,2))
K39	K39	=IF(B4="","",SUM(H37:L37))

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=Settings!$B$2:$B$28
B12:B18	List	=Settings!$F$2:$F$7
B21:B27	List	=Settings!$F$2:$F$7

Alex Blakenburg · Jan 7, 2024

Give this a try:
Do not assume the folder address you have currrently is correct.
Go to the folder in explorer, select the folder and then right click the folder then select copy as path, paste it into the New Folder line in the code

Rich (BB code):

Sub SavePayrollKeeper2024WithNewName()
    Dim NewFN As Variant
    PostToYTD
    PostToSocialSecurityRegister
    PostToPTORegister
    
    ' 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:\Payroll Keeper 2024\Earning 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("B6").Value & " - " & Format(.Range("B7"), "mm-dd-yyyy") & ".xlsm"
    End With
    
    wbThis.Sheets.Copy
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close
End Sub

Robert Wyatt · Jan 7, 2024

Alex Blakenburg said:

Give this a try:
Do not assume the folder address you have currrently is correct.
Go to the folder in explorer, select the folder and then right click the folder then select copy as path, paste it into the New Folder line in the code

Rich (BB code):

Sub SavePayrollKeeper2024WithNewName()
    Dim NewFN As Variant
    PostToYTD
    PostToSocialSecurityRegister
    PostToPTORegister
   
    ' 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:\Payroll Keeper 2024\Earning 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("B6").Value & " - " & Format(.Range("B7"), "mm-dd-yyyy") & ".xlsm"
    End With
   
    wbThis.Sheets.Copy
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close
End Sub

That works Beautifully thanks so much for the help.

Alex Blakenburg · Jan 7, 2024

Thanks for letting us know. That was definitely a team effort

Peter_SSs · Jan 7, 2024

Robert Wyatt said:
That works Beautifully thanks so much for the help.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

rollis13 · Jan 7, 2024

@Robert Wyatt
And so, what was the final name of the folder you used for the NewFN line?

Robert Wyatt · Jan 7, 2024

rollis13 said:
@Robert Wyatt, So, which was the definitive folder name you used in the NewFldr line?

"D:\Blank Payroll Keeper\Earning Statements"

rollis13 · Jan 7, 2024

Thanks @Robert Wyatt.
Now, could someone kindly point out how @Alex Blakenburg macro differs from most of the earlier suggested macros (I mean, contents, not syntax).

Alex Blakenburg · Jan 7, 2024

rollis13 said:
Now, could someone kindly point out how @Alex Blakenburg macro differs from most of the earlier suggested macros (I mean, contents, not syntax).

The discussion uncovered that there were 2 separate issues:
• Folder Name incorrect
• File Name incorrect
Having a copy of the file was unlikely to help in resolving the Folder Name issue.

The code I posted:
• Isolated the folder name to make it conducive to pasting in the folder name by-passing historical versions and typing errors.
Inlcluded covering off a possible point of failure by checking for and if needed adding the path separator.
• The file name was using an unqualified Range reference and was being put together after the workbook sheets were being copied and the new workbook became the active workbook.
The copy process changes the active sheet to be the 1st sheet in the workbook (unless the active sheet was already the 1st sheet)
In the code , moved the construction of the file name to be before the copy process as well as being explicit in terms of referencing the workbook & worksheet. The XL2BB also gave the sheet name so the code didn't need to rely on the active sheet.

rollis13 · Jan 8, 2024

Thanks @Alex Blakenburg, your macro is the best way to tackle the subject of the thread

but I'm sure that the conclusive was:

• Isolated the folder name to make it conducive to pasting in the folder name by-passing historical versions and typing errors.

Macro not working.

Robert Wyatt

Board Regular

Robert Wyatt

Board Regular

Excel Facts

Alex Blakenburg

MrExcel MVP

Robert Wyatt

Board Regular

Alex Blakenburg

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

rollis13

Well-known Member

Robert Wyatt

Board Regular

rollis13

Well-known Member

Alex Blakenburg

MrExcel MVP

rollis13

Well-known Member

Similar threads

Forum statistics

Share this page

Macro not working.

Board Regular

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP, Moderator

Well-known Member

Board Regular

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock