Housekeeping payroll that pulls rows of information to different sheets

If you add a "key" column formula to your database to add a unique numbered INDEX that relates to each sheet that row should go to, then it's easy to have the other sheets check that column and pull in the data based on the unique numbers.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook and I'll show you how. Make sure there is a good set of desensitized sample data. GO ahead and mockup on the payroll sheets some example rows as "results" if it's not patently obvious. Help us help you.

The one sheet added does not have the "mockup" of the information expected to appear on that sheet? "ANA" is on that sheet as an identifying code, but that code is not included in the data sheet.

Ok, you will need to provide on the sheets the required Team Assignments, so I added that in column H. Just list the teams you want listed on each sheet in column H and they will be included.

Also, since you have both static cells at the bottom and a dynamic number of rows to be copied in, I'm recommending you flip/flop your layout so the expanding stuff is at the bottom starting in row 10, now nothing below it to run into. The macro will clear everything from row 11 down and add new info each time it is run.

Option Explicit

Sub RateSheets()
Dim MyArr As Variant, LR As Long
Dim ws As Worksheet, wsAsgn As Worksheet

Set wsAsgn = ActiveWorkbook.Sheets("Assignments")

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> wsAsgn.Name Then
        With ws
            MyArr = Application.WorksheetFunction.Transpose(ws.Range("H:H").SpecialCells(xlConstants))
            wsAsgn.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=MyArr, Operator:=xlFilterValues
            LR = wsAsgn.Range("A" & Rows.Count).End(xlUp).Row
            If LR > 1 Then
                ws.UsedRange.Offset(10).Clear
                wsAsgn.Range("A2:D" & LR).Copy ws.Range("A11")
                LR = ws.Range("A" & Rows.Count).End(xlUp).Row
                With ws.Range("C" & LR + 1)
                    .Value = "TOTAL PAY:"
                    .Interior.ColorIndex = 15
                    .Font.Bold = True
                End With
                With ws.Range("D" & LR + 1)
                    .FormulaR1C1 = "=SUM(R11C:R[-1]C)"
                    .Interior.ColorIndex = 15
                    .Font.Bold = True
                    .NumberFormat = "$0.00"
                End With
            End If
        End With
    End If
Next ws

End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select RateSheets from the macro list.

Did you remember to add the Team Assignments in column H of each person's sheet like in the sample above?

Time to check your work, post the workbook where you've applied all this, make sure the macro is installed in it as well, I'll check it out.

Well, since the whole point was to copy "cleaning assignment" to the other sheets, and "cleaning assignments" is a blank sheet, can't imagine it working. Also, you've add a bunch of other sheets, so you'll need something consistent to flag which sheets to process and which ones NOT to process.

This should do it:

For Each ws In ActiveWorkbook.Worksheets
    If ws.Range("B3") = "CLOCKSPOT HOURS" And ws.Range("H1") <> "" Then
        With ws

Heh, thick skins go a long way. We're helping for free so you can forgive any text you read that sounds more condescending than it actually is.

Anyway, you've got the updated code snippet above required for the macro to ignore all those other sheets, it will only execute on sheets that have something in H1 and "CLOCKSPOT HOURS" in cell B3. Try the updates on your workbook with data in "cleaning assignments" to transfer and see how it goes.

THanks for the feedback. If that takes care of your original query, please select Thread Tools from the links above and mark this thread as SOLVED. Thanks.