Thread: 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

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

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

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

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.

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

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!

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

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

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

For j = 12 To Cells(11, 1).End(xlDown).Row

Also, remove, or reposition, sheet2 so that it is before sheet input. Otherwise it will try to process another blank sheet.

rylo

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

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

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

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.

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