+ Reply to Thread
Results 1 to 84 of 84

Charting Payroll

  1. #1
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    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)
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll-

    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.
    Please Login or Register  to view this content.
    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

  3. #3
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: Charting Payroll-

    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?

  4. #4
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll-

    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.

  5. #5
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll-

    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?

  6. #6
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll-

    Sorry see attached.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll-

    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.

    Please Login or Register  to view this content.
    rylo

  8. #8
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll-

    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.

  9. #9
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll-

    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.

  10. #10
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll-

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

  11. #11
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll-

    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.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Good point about the emp id rather than the name. I've updated to work on that, and enhanced to fill out the payroll sheet at the same time. I've not tried to manipulate the employee name - I'll leave that to you.

    Also, there were some IDs that didn't align in the data sheet to the input sheet, so I modified them to make the code run. Just found that some of the IDs have been duplicated. Would pay you to review your example file to make sure that you have things correct.

    See how it goes.
    Please Login or Register  to view this content.
    rylo
    Last edited by rylo; 12-20-2011 at 10:04 PM.

  13. #13
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    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.

  14. #14
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    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

  15. #15
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    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 ***.

  16. #16
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    For ease (on my part) and consistency, making a clerical time card would seem to me to be the better option. That way you have consistency, and only have to run one macro when everything is done. It also gives you better flexibility in that you can add all the "odd ones" to the one place and have them incorporated - aids expandability. Much easier than having to maintain either a reference table, or updating the code to be able to identify the manual items.

    rylo

  17. #17
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Ok agreed. Thank you so much for everything. I will be in touch. I sent you a private message please answer it. You really helped me out alot on this.

  18. #18
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Ok another task to this workbook. Can you make it that when I add a new employee to the input sheet (as I would for all new hires) that his ID number defines the name. Meaning that in each new time card all I have to add is the 3 diget Employee # in column B and the name associated with that number automatically generates in column C next to the number. Can you make this happen? (Attached is the current worksheet)
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Also I changed the clerical time card. I just ran Macro with the new clerical timecard and it did not carry the hours over. Probably a minor fix. Can you alos do this please. Thanks.

  20. #20
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    1) Input sheet - where would you expect the home Dept and Employee name to come from?
    2) Clerical time sheet - it isn't in the same format as the other data sheets, so nothing would be carried over. It must be in the same format, so the Reg Hours has to be in column BH, OT hours in column BI and total hours in column BJ. Try making those changes and see if it then brings back data.

    rylo

  21. #21
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Ok please see attached. I had an overtime dilema. I added more cells columns to the input sheet. You will see what I am talking about. Also I am stumped on something. In the time cards the total of daily hours sum up into the highlighted cell. Then it carry's over to the total hours or overtime hours if I have hours on Saturday or Sunday. My problem is this: On Monday, Tuesday, Wednesday, Thursday, and Friday if any employee work more than 8 hours it is considered OT. Can you forulate the OT Totals to carry over the amount of hours over 8 hours with still keeping the actual number of hours worked that day in the highlighted cell. Attahced is the most current time card. Thanks again if you can help. I made this as if the formula worked in the 033-11 time card, and the clerical time card.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    1) Home Dept would be manually put in and employee name would manually be put into the input sheet for any new hire. Then when i am in a new time card I would like to just enter the employee ID # and the name would pop up. Just saving me from entering the name each time. If it can be done then great.
    20 I will do this now!

    Thank you so much.

  23. #23
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Post 21:
    BH11: =MIN(SUM(K11+S11+AA11+AY11+BG11),40)
    BI11: =SUM(K11+S11+AA11+AY11+BG11)-BK11+SUM(AI11+AQ11)

    Post 22:
    You could probably do this with an event macro that fired when something was entered in column B. It would take that value and get the other 2 values from the input sheet. This would add some considerable overhead as it would have to fire off every time something was added / changed to the sheet. Would this be acceptable?

    Another way would be to only enter the ID number, and then run a completely separate macro to fill in the missing details as an action on its own. You could put in all the IDs, then run that macro to get the details then continue with the data entry.

    Do you have a preference?

    rylo

  24. #24
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    See Attached!
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Post 21-
    Did not work. The formulas did not seperate hours over 8 per day as OT Hours.

  26. #26
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Post 22-
    I think the former is better. Reason being is that I can get lost by just entering numbers vs names. I will try this first.

  27. #27
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Can you update your example file with the formulas, attach, and make sure that you explain what result should be in which cell, and how that result should have been achieved.

    rylo

  28. #28
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    I think you had a typo....
    =SUM(K11+S11+AA11+AY11+BG11)-BK11+SUM(AI11+AQ11)

    should have been

    =SUM(K11+S11+AA11+AY11+BG11)-BH11+SUM(AI11+AQ11)

    This make sense. It worked.

  29. #29
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Nevermind still dont work. 40 hours are not moving from the regular hours.
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Wait i see. Clerical over 40hrs is OT. The Employees are OT over 8 Hrs each day period. So Saturday/Sunday is OT and any day that totals over 8 not 40 as a whole. Sorry is I didnt make that clear.

  31. #31
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Example if they work 10 hours on Monday and have no other hours for the week they have 8 regular hours and 2 OT hours.

  32. #32
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    H

    OK, try
    BH11: =SUM(MIN(K11,8)+MIN(S11,8)+MIN(AA11,8)+MIN(AY11,8)+MIN(BG11,8))

    If I change BD11 to 6 (from 8) then it will give me 38,16,54 and if I change BD11 to 10 it will give me 40,18,58.

    Is that right? If not, then give specific example of what result should be obtained for what combination of numbers.

    rylo

  33. #33
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Ok that worked perfect. Thank you so much. I attached it in use. Can you please formulate the OT hours by dept as well. So in the input page instead of having the total hours per dept carried over the reg hours and the OT hors per dept would carry over. Does this make sense?
    Attached Files Attached Files

  34. #34
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Here goes.
    Please Login or Register  to view this content.
    rylo

  35. #35
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Rylo your a genius. Unreal man. Worked beautifully. Ok so the last thing. Can you make it that if I inout the 3 diget Employee ID # in the B11 cell the C11 cell will generate the employee's name. The dept in the A11 cell will be inputted manually. This will apply for all time cards (including clerical). Thanks again so much. I owe you big time.

  36. #36
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Also how do you freeze 2 rows? And can you freeze one row and one column on the same sheet?

  37. #37
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Put the following worksheet change event on any existing sheets, then put in an id. Also put it onto the "Blank" sheet, so that it will be copied across to any new sheets created.

    Freeze Panes:
    Put your cursor in say 033-11!A11, then View, Freeze Panes, freeze Panes. This will do a row freeze at that point.

    rylo

  38. #38
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Not following you. What following worksheet change event?

    Freeze panes. I know how to freeze a pane I was wondering if you can freeze two consecutive rows in a worksheet and if you can freeze a row and a column on the same worksheet.

    I want to freeze rows 1 & 2 of the input sheet but I only am able to do just the 1 row.

  39. #39
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    My bad

    Please Login or Register  to view this content.

    Re the freeze panes, what happens if you select A3 then do the freeze panes? It should freeze rows 1 and 2.
    rylo

  40. #40
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Thanks I'm on it now.

  41. #41
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    I cannot figure out how to put a change event into this work sheet. I looked up how to do this and i am hitting blanks. Can you write how to input a change event. It keeps taking me to the bbb macro.

  42. #42
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Select the tab for the data sheets (say 333-11), right click, view code, and paste in the event macro. You will have to do this for every existing data sheet, and also the "blank" sheet you use as a template.

    Then when you put in a new ID, or change an existing one, the name should populate from the Input sheet. If you don't already have the id in the input sheet, then the code will error.

    rylo

  43. #43
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Well that was easy. Any way the name populating with the first name first even though the input page has the last name first? I am a greedy ******* arent I!!!!!

  44. #44
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Here's a christmas present for you. Go back to post 7, and you will see in the code how I rearranged the name, assuming that there is a space to do the separation.

    Have a go at rearranging things yourself......

    rylo

  45. #45
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Believe me i am an overachiever and I dont give up on anything. I can figure out formula's farely easily but the code is different man. I am paying close attention but am unable to see the consistensy or pattern. How about a hint????? Also if I change this will it just flip the name from Last name first to first name first in just the time cards (clerical and 033-11 etc..)? That is what I want to accomplish!

  46. #46
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    The relevant line is
    Please Login or Register  to view this content.
    What this is doing is taking the last character in the string (right.....), concatenating it with a comma space (", ") and then taking all the characters from the left but excluding the last 2 which contain the existing space initial (" ?")

    It does assume that the existing name is in the form surname space initial.

    So in the event code the main line is putting the existing surname space initial into the cell immediately to the right of the ID.

    You can then take that value, rearrange it as per above, then paste it back in the rearranged form.


    Does that help?


    rylo

  47. #47
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    I am lost. But its becoming clearer. So I change the bbb macro or add this line to the worksheet change event?

  48. #48
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    I was just inputting the line in change event and no such luck. I searched the latest bbb macro and did not even see this line in it any more. I am sorry to disappoint you man.

  49. #49
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Here's the modified worksheet_change event code in its entirety. Just replace the existing event code with this one.

    Please Login or Register  to view this content.
    rylo

  50. #50
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Worked beautiful. I would not have figured that out. Damn man that worked great. Thanks again for everything.

  51. #51
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    If everything is now done on this post, can you please make sure you have marked it as SOLVED.

    rylo

  52. #52
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Actually I just tried it on my copies of the time cards which has my employees full last names and it cut most of the name off. Left it like this- rell, Michael H

  53. #53
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Letters of this members last name is missing. Not done by me but done by the formula.

  54. #54
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Example workbook time....

    rylo

  55. #55
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    hahaha. No doubt. I will try to figure this out.

  56. #56
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    NO LUCK. I cannot make sence of it. I threw all the codes up there and tried so see the patterns and no luck. What am I doing wrong?

  57. #57
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    you will have to attach an example workbook with all the code, so I can have a look.

    rylo

  58. #58
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Ok see attached. You will see that when you have just a (1) character last name it translate over perfectly but if you have more than (1) character in the last name then it does what is on the attached worksheet. Have a look. I only put one letter of the employees last name for safety to them but in my system they go by the full last name.
    Attached Files Attached Files

  59. #59
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Here's another version of the event code. It does assume that the details on the Input sheet have only one comma (,) separating the last name from the first name(s).

    Please Login or Register  to view this content.
    rylo

  60. #60
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Your the best. Just tried it. Congrats on the Aussie open. Hell of a tournament. I cant wait for the US Open. Right in my backyard! Thanks again.

  61. #61
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Hello there. I just tried running payroll and ran the below Macro and it kicked back. I believe its the 007-12 tab. That is a copy of the blank tab. I will be adding numerous tabs due to each project gets its own tab (copy of blank tab)




    Sub bbb()
    Dim OutSH As Worksheet, PaySH As Worksheet
    Set OutSH = Sheets("Input")
    Set PaySH = Sheets("Payroll Report")
    For i = OutSH.Index + 1 To Sheets.Count
    Sheets(i).Activate
    For j = 11 To Cells(11, 1).End(xlDown).Row
    'process input
    Set findit = OutSH.Range("A:A").Find(what:=Cells(j, 2).Value)
    outrow = findit.Row
    outcol = WorksheetFunction.Match(Cells(j, 1), OutSH.Rows("2:2"), 0)
    OutSH.Cells(outrow, outcol).Value = Cells(j, 60).Value
    OutSH.Cells(outrow, outcol + 1).Value = Cells(j, 61).Value

    'process payroll
    Set findit = PaySH.Range("A:A").Find(what:=Cells(j, 2).Value)
    If findit Is Nothing Then 'add a new record
    outrow = PaySH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    PaySH.Cells(outrow, 1).Value = Cells(j, 2).Value
    PaySH.Cells(outrow, 2).Value = Cells(j, 3).Value
    PaySH.Cells(outrow, 3).Value = Cells(j, 1).Value
    PaySH.Cells(outrow, 4).Value = Cells(j, 60).Value
    PaySH.Cells(outrow, 5).Value = Cells(j, 61).Value
    Else
    outrow = findit.Row
    outcol = PaySH.Cells(outrow, Columns.Count).End(xlToLeft).Offset(0, 1).Column
    PaySH.Cells(outrow, outcol).Value = Cells(j, 1).Value
    PaySH.Cells(outrow, outcol + 1).Value = Cells(j, 60).Value
    PaySH.Cells(outrow, outcol + 2).Value = Cells(j, 61).Value
    End If
    Next j
    Next i

    PaySH.Activate
    Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Sort key1:=Range("B2"), Header:=xlNo
    End Sub

  62. #62
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Can you attach a file and explain exactly what your did when the macro errored.

    Also, can you please edit post 62 and include code tags.

    rylo

  63. #63
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Ok see attached and run the macro. This will explain what my issue is.
    Attached Files Attached Files

  64. #64
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Its fallen over as there is no data in the sheet to process. Is this likely to be the case? If so, then what is it meant to do?

    rylo

  65. #65
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    I dont really follow you. All the pages are meant to do just what the clerical page does. I use the blank page to copy from and add them to the end of the workbook. Then i rename them and input my payroll hours. When I run the macro I would like all the payroll pages including the clerical page to calculate into the input sheet and the payroll report sheet.

  66. #66
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    I run the Macro, then I get a popup and it shows a runtime error, I hit debug and this pops up:
    Attached Files Attached Files

  67. #67
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Ok see attached. This is my time card for this week. I had to manually inout the input sheet and payroll report sheet but this is what the finished product should look like. Hope this helps!
    Attached Files Attached Files

  68. #68
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    All the posts to date have related to completing partial entries that are in the sheets. That is what the code is meant to do, both the full macro, and the change event.

    What is happening is that the code is running on a sheet THAT DOESN'T HAVE ANY DATA in column B, so it is erroring out.

    If you are now saying that you are going to have sheets with no data, then the code will have to be modified to handle that situation.

    So is it likely that there will be sheets with no data?

    rylo

  69. #69
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Only the sheet titleted blank will have no data. The others will look like the 2-7-12 time card I sent to you. That will be typical. May have a few more sheets added to it for more projects but that sheets named 004-12, 006-12, 007-12 etc are pretty standard

  70. #70
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Looks like the structure of the sheets has changed from when the bbb macro was originally written. Run it from row 12 not row 11. So the for loop will be
    Please Login or Register  to view this content.
    Also, remove, or reposition, sheet2 so that it is before sheet input. Otherwise it will try to process another blank sheet.

    rylo

  71. #71
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Thank you. i will try this in the am. I really appreciate it.

  72. #72
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    No good. See attached and run the changed macro. It recognizes the employees but does not input there hours.
    Attached Files Attached Files

  73. #73
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Yet another change to the file structure since the macro was created. The Reg Hours were originally (and are still in sheet Clerical) in column 60 (BH) However these seem to have moved to column 67 (BO). That is why it doesn't seem to be returning anything.

    How about you standardise your structure for all sheets, Update the macro for the changed locations, and see how that goes.

    rylo

  74. #74
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    The way the 004-12, 033-11, 007-12 etc sheets are structured are the way I need to keep them. I rearranged the clerical sheet prior to accomidate the blank sheet. I am surprised that this is happening. Can you help with fixing this. I really dont understand what you meant in the previous post. This was working earlier in December but something is causing it to malfunction.

  75. #75
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    What I'm saying is that the data sheets all have to have the same shape and IT CANNOT CHANGE. At this stage, they have changed from the original example file. If 004-12 is now in the right shape, have all the other sheets been changed to this structure, and will all future sheets have this structure.

    If they are not likely to be the same, or are likely to change, then advise accordingly, and also advise what would be constant. Maybe the relevant headings?

    rylo

  76. #76
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    They should all be like 004-12. The clerical one is the only one that is a little different. What should I do next?

  77. #77
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    Have you amended all your sheets to be the same structure as 004-12? If so, then adjust the macro to reflect that you are extracting data from column 67 rather than column 60 and see how it goes.

    rylo

  78. #78
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Ok I ran it. I deleted the clerical worksheet which I will add later as a regular sheet. Only one issue here. Check job sheet 012-12. The employee worked 10 hours on Saturday which should have transfered to the input sheet as 11J OT and then the payroll report as 10 hours OT in dept 11J. It did everything it should but showed 0 hours. Maybe a simple fix. Im not sure. Can you take a look at the attachment.
    Attached Files Attached Files

  79. #79
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Hi

    The overtime hours should be coming from column 68, not column 61.

    rylo

  80. #80
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    Hi thank you. I ran that and I believe there was a glitch. Please run it. You will see what I am talking about.
    Attached Files Attached Files

  81. #81
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    Opened file, ran macro bbb, program ran to completion with no errors.

    Can you be more specific on the "glitch".

    rylo

  82. #82
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    ok run this. You will see brian and kevin's hours in 1 department but they are split when they shouldnt be. Same dept.
    Attached Files Attached Files

  83. #83
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Charting Payroll

    so you are now saying that when the hours are transferred over to the payroll report sheet, if there are already any existing hours for a particular department for that employee, then any new hours should be added to the existing hours, not appended to the right? If so, then this hasn't been mentioned before that I can recall or find in any post.

    rylo

  84. #84
    Registered User
    Join Date
    12-19-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Charting Payroll

    No that is not the case. There is just a glitch. Those 2 particular employees hours get screwed up. If you run it you will see the error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1