Automate the payroll process using Excel

Hi Experts,

I am trying to automate my payroll process and would like to get your help on how this can be accomplished.

Right now, we do daily reconciliations and each workbook contains a simple table with the employees name in the header and their total in the bottom row. The workbook name is based on date ie "05.01.18"

In the pay summary workbook, i do i a hlookup based on the name match and return the total for that day.

But every 2 weeks, I have to go into each cell and edit the link to match the date so it will pull the correct total for that day.

Is there a way this process could be automated? I am familiar with VBA and would not mind a VB based solution but would rather use Excel formula's if possible.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you supply a workbook that shows "before" and "after" sheets?
jnsimexAuthor Commented:
Sure I will upload the daily reconciliation workbook for one day (April 21) and the pay summary workbook.
Martin LissOlder than dirtCommented:
Do you have a "Week-Ending" workbook for each employee or are they all in one workbook?

Do you want a button on the "Week-Ending" workbook that transfers the data from the daily worksheet, or do you want a button on the daily workbook that transfers data to the "Week-Ending" workbook?

BTW the Comments in the headings of columns '"B" and "C" in the "Week-Ending" workbook seem to be wrong.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jnsimexAuthor Commented:
Yes I have a week ending workbook for each employee.

I think having the button on the Week Ending workbook would work better for me as i have been using it to do the hlookup to the daily sheets.
Martin LissOlder than dirtCommented:
Since your daily workbook contains information for all the employees, IMO it would make more sense to have just one weekly workbook (with a page for each employee) because then all the data could be input with the click of just one button, but of course it is your choice.

Any button that I add will need to have code and that means that the workbook to which it is added will need to be saved as an xlsm file. Is that okay?

Will the daily workbook be in the same folder as the weekly workbook?

Can more that one daily workbook exist at the same time?

With the daily workbook name always include the name of the employee?
jnsimexAuthor Commented:
Hi Martin, actually does make more sense, one workbook with separate sheets for each employee. I can just send the sheet instead of the whole workbook when I do the payroll. Great suggestion :)

xlsm folder file is okay.

The daily workbook and weekly workbook are in different folders but they share the same parent folder.

Yes the daily workbook will always include the name of the employees regardless if they are working that day or not.
Martin LissOlder than dirtCommented:
I'm working on this now and I'll have a solution for you later today.
Martin LissOlder than dirtCommented:
Try the attached workbook. It assumes that the name of the daily timecard will always contain a date and that the summary data in the daily timecard will always be in row 19. In the weekly workbook you will need to add a sheet for each employee like the one I created for Jean. The case of the name doesn't matter. Note that you won't need any formulas in columns 'D' or 'F'. To update all the sheets just hit Ctrl+Shift+T from any one of the weekly worksheets.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jnsimexAuthor Commented:
Thanks so much for your help so far!

i will definitely try the workbook later tonight when i get infront of a computer.
jnsimexAuthor Commented:
This worked like a champ!

Thank you so much for the taking time out of your day to put this together, I really appreciate it :)
jnsimexAuthor Commented:
Martin was able to create a macro that pulled everybody's daily total into a pay summary sheet in a quick and efficient manner.
Martin LissOlder than dirtCommented:
I don't know if you saw this line in the code, but as it suggests you can enter the path to the daily files which will save you a keystroke or two.
dlgFile.InitialFileName = ThisWorkbook.Path 'or something like "C:\folderA\folderB"

Open in new window

In ay case you’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)
jnsimexAuthor Commented:
Yes I hard coded that line to point to the daily reconciliation folder.

I took a look at your profile and will definitely have a read of your

Thanks again for your help and have a good night.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.