Macros for Payslip Generation

[MACLARKE]

Hi, I'm looking for some help please. I know nothing about VBA coding/macros and need some help formatting an excel spreasheet to do the following:

1) Calculate payroll per employee
2) Generate Payslips
3) Print to PDF

I have a spreadshhet with 3 tabs... Payroll Info (Name, address etc), Payroll Calcs and the Payslip template. From what I have read online, I think I can use VLOOKUP to match the info and calcs to the payslip but can someone help me with the VBA code for the looping and printing to PDF. I would like to have one button to Generate & Print All Payslips.

The excel file is attached.

Your help will be much appreciated as I have hundreds of employees to process

[MACLARKE]

Hi,

Can anyone assist me? I've made some more progress on my own but need help to pull it together.

To recap,

I am trying to build a spreadsheet that will create Payslips for employees. the spreadsheet attached is the one I have been working with. It has 3 tabs - 1) Payroll Info (General Staff name, Bank details, etc) 2) Payroll Stmt - (statement template to be generated for each employee) & Payroll Calc (calculation sheet).

I have already created some sub routines that work and included them in the word file attached, but I really need help to pull this all together.

1) Pull in relevant data from Payroll Info (the macro I started for this populates the info, for the first employee but I am having problems with the loop)
2) Pull in relevant data from Payroll Calc
3) Generate Payslips for each employee by clicking button (One click to generate all) - I can link the button myself once all macros are set
4) Save to Invoice file on C Drive as a pdf (I got this to work, included the steps in the Word file)

I would really appreciate your help with this. Thanks.

[skywriter]

I could probably help you but you have to do two things.
1) Make your payroll info sheet and your payrollcalc sheet match what's going to go on your payrollstmt. Examples are on your payroll info sheet you have two Jane Does, don't your employees have unique emp numbers or SS# that will be on all three sheets so we know we are dealing with the right person? You also have stuff on your payrollstmt sheet that I don't see anywhere on the other two sheets. National Insurance. If National Insurance is the same as Insurance (Employee) then please make them the same.

2) Use actual numbers in all sheet. In other words mockup an employee payrollstmt with actual numbers that match the other two sheets so we can see what goes where and make it realistic don't put 100 in every box. Different numbers for all would be nice then it's very apparent to us what goes where. Also make the title of the payrollstmt sheet what you would want it to be after it's created for an employee. I'm assuming something to do with employee ID number and payroll date, just be advised some characters like / are not allowed in sheet names. There is also a character limit of 31 so think up a name that gives you enough information but doesn't go over that limit. Maybe 2015Jun26Emp123456... it's your choice just be careful with charactger types and lengths. You can't have 6/26/2015.

Get those issues squared away and we can help you.

We are also going to need to know the full path to the file where you want the PDF stored and also how do you want to name the file? Examples would be they all would start with PayrollRun and then maybe the date you run it. PayrollRunJune26...., you specify. Or maybe you want an input box to come up and ask you for a file name?

Thanks.

[MACLARKE]

Hi Skywriter,

I'm so happy for your willingness to help.

It seems that my spreadsheet may have been a bit confusing... so I have updated all info per employee to be on the same sheet. (The previous format was to populate the stmt with info from both sheets). The columns highlighted in yellow are to populate the fields I2:I5 on the PayrollStmt. The columns higlighted in blue are related to the cells for earnings and green for the cells related to deductions. For example, for regular earnings Cell C14 and D14 on the payrollStmt will be populated by F3 and E3 respectively from the PayrollCalc tab. I've included a MOCK spreadsheet with the info for Doe, Jane.

Payroll will be processed bi-monthly on the 14 and 28. How will the Payroll Period field be populated?

The path to save the file is:

C:\Invoices\Payroll\Current month

You can save the files in this format:

Employee name_yyyymmdd.

I hope this is easier to work with.

Thanks again!!! Let me know if I need to update anything else.

[skywriter]

It would really be helpful if we could either reorder the payroll calc sheet or the payroll statement.
On your payrollcalc sheet you first 4 columns are fine they are in the same order as how they go on the statement from top to bottom, but then we get to your regular hours and regular rate and hours come first but on the other sheet they come second, then your assignment earnings are in the row below but come from column O on the payroll calc sheet. I could write some nice tidy fast code if these things were in the same order on both sheets.

Following the PayrollStmt sheet an example would be
Regular Earnings, Rate, Hours, YTD
Assignment Earning .....
Overtime 1.5 Rate, Hour, YTD
Overtime 2
Performance Bonus

Etc. etc.

Thanks.

[MACLARKE]

Thanks Skywriter!

Here is the revised file.

[skywriter]

That's awesome, thank you very much.
A couple of other questions. Don't worry I'm working on this, I just see a couple other things that I'm wondering about.
1) You posted this earlier "C:\Invoices\Payroll\Current month", so my question is where you say current month what exactly do you want the name to be and are you going to create the folder or do you want a popup to ask you if you want to create a folder. If you are going to create the folder then I need to write logic that will save to the right folder based on the date you run the macro.

2) You have a cell B27 that have a label Pay Period above it. Do you want a popup box to come up before you run the macro to ask for the date to be put into that box?

3) Do you want each sheet to be it's own PDF file and do you want me to delete the worksheet from the Excel file?

I will hopefully have this ready for you to test sometime today.

Thanks.

[skywriter]

You can run this and check it for accuracy.
If you want to run it more than once, then make sure you delete the sheets it created or you will get an error.
I asked you some questions in post #7 above, if you can answer those we can finalize this. I did some testing on my computer with saving these as PDFs and it works fine, we just need to customize the macro to your needs.

Thanks.

[MACLARKE]

Skywriter! This is perfect!!

I checked the accuracy and all is well. Thanks you sooo much, I wish I was tech savvy like this...

To answer your questions:

1) Current month will be the name of the month the payroll file is being populated for. You can create a pop up and I can input the month e.g. June 2015.

2) Cell B27 - Yes you can create a pop up and I can input the range of dates the payroll covers. E.g. 01/06/15 - 14/06/15

3) Yes, please save each pay statement as an individual pdf and also a Master pdf file will all included (for Management record retention). You can name the Master file: PayrollMasterPDF_yyyymmdd

Just a quick question, this logic is set to run for all employees listed right? So if we have 40 employees in one paycycle then increase to 50 in another, we won't have an issue?

Again, thanks so much. I really appreciate you taking some time to help me with this!!! This will be such a blessing in terms of time management!

[skywriter]

Just a quick question, this logic is set to run for all employees listed right? So if we have 40 employees in one paycycle then increase to 50 in another, we won't have an issue?

Yes it will run fine. The only thing to be aware of is that I coded it with rows 1 and 2 being header rows. If you add another row or take away a row there will be issues. Blank rows between employees or between the header row and the first employee will cause issues also.

The whole thing is coded to the way it is now. The payrollstmt sheet needs to stay the way that it is including the name, same for the payrollcalc sheet. Change names, move things around, add columns etc. and there will probably be some issues.

You didn't answer the question about deleting the Excel sheets created in the Excel file, I will assume you want them deleted since you want a master PDF and individual PDFs.

[skywriter]

Okay give this a test run. This should create the current month folder if it doesn't exist and will delete the files from the Excel worksheet so you can reuse it.

If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.

If you are happy with my help, please consider clicking the add reputation button in the lower left hand corner of this post.

[MACLARKE]

Ok, thanks. Let me try it out.

[MACLARKE]

Okay give this a test run. This should create the current month folder if it doesn't exist and will delete the files from the Excel worksheet so you can reuse it.

If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.

If you are happy with my help, please consider clicking the add reputation button in the lower left hand corner of this post.

Works like a charm!!!!!!!!! Thanks again SKYWRITER - I really appreciate this. I will definitely add reputation - in my opinion, your assistance was 5-Star!!!

[MACLARKE]

Hi SKYWriter,

One thing I just noticed... the Pay period did not populate on the individual or master PDF. Can you check it for me please?

[skywriter]

Hi SKYWriter,

One thing I just noticed... the Pay period did not populate on the individual or master PDF. Can you check it for me please?

I don't have a problem. You should get an input box asking you to enter whatever you want to see and then click okay and it should show up. I've retested the file and I get that in all sheets and the master sheet.

You are getting the input box?
What exactly are you typing in?

[MACLARKE]

I am getting the input box and I typed in 01/06/15 - 14/06/15. Clicked ok - no error message.

Do I need to change the format of my input?

[skywriter]

What are you getting in the result, anything?

Are you by chance using a MAC computer?

Are you willing to try some troubleshooting at my direction?

Our other option is to remove that part of the code and you could just enter the information into the template sheet before you run the macro.

[MACLARKE]

Nothing populated in the box. I am not using a MAC.

Sure we can troubleshoot... let's try...

Worst case we can remove that part of the code but I'm sure it may be something simple I need to adjust.

[skywriter]

So to get into the code window you hit Alt + F11.

First thing to do is find this line of code
Application.Calculation = xlCalculationManual
Type an apostrophe in front of the first word '
'Application.Calculation = xlCalculationManual

Then continue on.

You should see the macro it's title "PayrollToPDF" if you click into the title to get the cursor there, then push F9. You should now have the text highlighted by a rust colored background and a large dot of the same color in the left margin.

Now find this line, it's about 14 or 15 lines down.
strPayPeriod = InputBox("Please enter the pay period", "Pay Period")

click into the line and push F9 and you should have that same rust color background with the dot in the left margin. These are called breakpoints.

If you have dual monitors it would be good to have the code in one and the spreadsheet in the other. Alt+F11 to go back and forth between code and sheets.

If not go no problem, just go back to the sheet and click the macro button to start the macro.
It should take you back to the code with the first line of the code highlighted in yellow. Now press F5 and it should jump to the second break point.

Now I want you to switch to F8 instead of F5, very important to use F8 from this point.


When you push F8 now the input box should popup, go ahead and enter something and press okay, it should now jump you back to the code.

Now I want you to hit Control + G and a blank window should pop up.
Type this ? strPayPeriod in that window and hit enter. Everything from the question mark through the word period. You can just copy if it's easier. The value that you entered into the input box should show up below the line you typed after you hit enter.

If that seems okay then continue hitting F8 and the code will keep moving down line by line.
When this line is highlighted:
.Cells(27, 2).Value = strPayPeriod then hit F8 one more time until the line below it is highlighted.

Now go back to the spread sheet you should see a new sheet with the first employee on your calc sheet as the name of the sheet. You should now see the value you typed in the input box in B27 of the new sheet.

You can now go into the code window and go the menu titled "Run" and select reset, close the workbook and choose not to save it.

[MACLARKE]

Hi Skywriter,

I got consumed with work over the past week closing out the quarter so I'm just getting back since Monday.

I tried the steps above. After the option to click the macro and cod highlighted in yellow, I pressed F5. Instead of jumping to the second break point, I got Run time error 76 - Path not found. What do I do from here?

[skywriter]

I need to know what line was highlighted when you got the error.

To make sure do the trouble shooting again and just use F8 to step through line by line and then tell me the error again and what line it's happening on.

[MACLARKE]

Hi Skywriter,

The reset worked.

Looking at the code, I think I got the error this morning because the computer I ran it on, did not have the Invoice Folder setup on the C Drive. After the reset, I ran it again and the sheets populated fine with the input, so we can call this matter closed.

Thanks again so much for your help and guidance.