Thread: Charting Payroll

Ok please read the following message I have sent out to many. I need to find out how to accomplish this. My IT guy was no help my payroll company was no help so now I am asking here. Message below explains what I sent to my IT guy-

Hey man how good are you with excel. I have a very tricky thing I want to accomplish. I attached something here. I have (3) mock up time cards along with an input sheet that I would like to pull data from the time cards. Then there is a payroll report sheet that would refine the input sheet. What I am trying to achieve is having 1 sheet that would have the employee’s ID #, name and hours he worked in specific dept’s.

I completed it how it should be but I don't know what formulas to make it happen. So the page titled Payroll Report is what I want to achieve from numerous time cards numbered 001-12, 003-12, 004-12 etc. The Input Sheet is there to help filter the time cards then the payroll report sheet would be the refined page. If you feel this is something we can do over the phone rather than coming to my office like we planned then that would be great. I need it done ASAP though. I have tried basic macro’s and pivot tables but they were unsuccessful. I want to do this with you not just have it done. I need to learn this because it would change a lot. Please let me know if you can accomplish what I want to achieve here. Sorry for the novel. (see attached)

Hi

Firstly, rearrange your sheets so that Blank is the first, followed by Payroll Report, Input then the data sheets.

Then clear out the data from Input. I'm assuming that you will have the headings already in place in the output.

Then run the following macro.

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Input")
  For i = OutSH.Index + 1 To Sheets.Count
    Sheets(i).Activate
    For j = 11 To Cells(11, 1).End(xlDown).Row
      outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      OutSH.Cells(outrow, 1).Value = Cells(j, 2).Value
      OutSH.Cells(outrow, 2).Value = Cells(j, 1).Value
      OutSH.Cells(outrow, 3).Value = Cells(j, 3).Value
      outcol = WorksheetFunction.Match(Cells(j, 1), OutSH.Rows("2:2"), 0)
      OutSH.Cells(outrow, outcol).Value = Cells(j, 62).Value
    Next j
  Next i
  
End Sub

This will bring in the data from the individual data sheets, and put it into the Input sheet. I notice that there are 2 entries for Kevin G from 2 different departments. What do you want to do about this? Leave with the 2 entries? Or is it an error?

See how this goes for starters.

rylo

Working with automation for payroll and timesheets can really be a pain in the butt. When/how is the information entered into the 001-12 timesheets? Is it possible to refine the entry on that side to make it easier to flow into the resultant sheets?

rylo thank you. I will try in the am. Kevin G worked in two different departments. My company is Union so this particular General Foreman (G) worked on Long Island (10G) and Boston (20G). They have 2 totally diffent pay scales which makes it real difficult eo input payroll as a book keeper who is not the project manager on the job. I input the time like this so I can chart all the different types of work we do in a single work week per employee per department. My foreman send me a timecard that looks similar to my blank and I job # it (001-12, 003-12, 004-12 etc...) and input the hours.

Bigbas-
as long as I input my hours daily per job and per type of work I am fine with restructuring it but I need time cards per job.

Thank you both for replying. This is very complicated at first but really it is pretty simple. Its just union which throws a wrench in it.

Rylo-Holy crap it worked. I added another sheet and still functioned great. Ok so now i get greedy. My input sheet should have all active ADP employees in it. And the breakout going left to right on who worked where. So Keving G should only be inputted on one line. Actually only his 30 in 20G, and 10 in 10G should have been entered. All names should be a fixture in the C column in alphabetical order. the payroll report should then refine the input page as to only the employees that had hours and in what few dept's those hours were in. Do you get that. I know its alot but it is real helpful to what I do. Can you still help with this?

Sorry see attached.

Hi

Try this. It does assume that all the names are already on Input, and the format of the names on the data sheet is consistent and can be reconfigured to match the pattern you have shown. If the real data is more complex, then things will have to be reconfigured.

Also, what do you want to do when there is a name on the data sheet, but it doesn't exist on the input sheet? The code below will work up to the last entry on sheet 004-12, as A, John does not exist on Input.

Sub bbb()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Input")
  For i = OutSH.Index + 1 To Sheets.Count
    Sheets(i).Activate
    For j = 11 To Cells(11, 1).End(xlDown).Row
      thisname = Right(Cells(j, 3), 1) & ", " & Left(Cells(j, 3), Len(Cells(j, 3)) - 2)
      Set findit = OutSH.Range("C:C").Find(what:=thisname)
      outrow = findit.Row
      outcol = WorksheetFunction.Match(Cells(j, 1), OutSH.Rows("2:2"), 0)
      OutSH.Cells(outrow, outcol).Value = Cells(j, 62).Value
    Next j
  Next i
End Sub

rylo

I will always match the employees that are on the time cards in the input sheet. My mistake John A was really Jonathon A. When i receive a W4 I they will immediatley be put into ADP as well as my input sheet. The payroll report sheets should be blank at first and when I run the Macro it will have just the employees that had hours in the input sheet ONLY. I will try this right now. Stay tuned. Thank you so much FYI.-Joe.

I am receiving a runtime error '1004'
Unable to get the Match property of the Worksheetfunction class

continue end debug help

continue is faded out.

Nevermind. I messed up by copying a time card. I just made a new one and it worked fine. I will examine it now.

It is working great. Ok I messed up a time card and put last name first on accident. It messed it all up. I fixed it and then it corrected it with putting first name first. But it brought something up to me though. The program should recognize employee number and not name. That would be wiser dont you think? Ok so now onto the payroll input sheet? Can you please help me with this. (See Attached) the payroll sheet is now blank. It should have only the employees from the input sheet that have hours for the week and in the dept that the hours were in. The first sheet had a mock up of how it should look completed.

Ok i adjusted the ID numbers. Good eye on that. This worked incredible. One more issue though. I have office employees that do not get put into time cards. I planned on adding them to the input sheet manually. Can you make it that anyone can have hours added to the input sheet manually and they carry over into the payroll report sheet? Otherwise it is 100% perfect. Thank you so much.

Hi

when would the manual items on the input sheet be done? Before bringing in the data from the data sheets, or after? And how would the manual entries be identified?

rylo

Departments 100, 110, 120, 200, 210, 220, 250, 300 would be manual. I can enter them at any point before of after. I can also just make a clerical time card if this is a pain in the ***.