Copy a row from one worksheet to another, based on criteriaSolved/Closed

Question

Hi Im a new user and im trying to make a roster sheet and really need some help any help is absolutely  appreciated.
I have some good formulas already set on 2 separate sheets that calculate the amount of people toward the manager ID and subtract in another column by total of unexcused from the master sheet to give a valid amount of people on site.
However I would like it a little more detailed

in the 2 non master sheets i have a few sections where I would like for the person to auto populate based on criteria of their manager and unexcused "X" in the  column in the master sheet with all of the data in that row in the master sheet going  under each managers section.

Ive been working on macros and different formulas but nothing seems to work.

I have a huge headache and would appreciate any help given. :)

TrowaD · Answer

Hi Amegolas,

I have a hard time understanding what you are trying to achieve. Could you give examples of how your sheets look like, how you want them to look and the steps necessary to get there.

You could post screen shots, and/or post your workbook (careful with sensitive info) using a free filesharing site like www.speedyshare.com or ge.tt and then post back the download link.

Best regards,
Trowa

TrowaD · Answer

Hi Amegolas,

First some things I noticed. 
On sheet 1 from left to right and from top to bottom you have: A, B, D, E, C, F.
I changed this to: A, B, C, D, E, F.

F is also present on sheet 2 but is ignored by the code to prevent duplicates.

Also to not mess up your format, the code doesn't insert new rows. So keep in mind that when you have more data to paste then rows reserved, headers and eventually other data will be overwritten.

Try the following code and let me know how you like it, just don't let that headache come back ;).

Sub RunMe()
Application.ScreenUpdating = False
Sheets("Sheet4").Select

For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    If cell.Value = "A" And cell.Offset(0, 3).Value = "X" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
        Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).End(xlUp). _
        End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ElseIf cell.Value = "B" And cell.Offset(0, 3).Value = "X" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
        Sheets("Sheet1").Range("I" & Rows.Count).End(xlUp).End(xlUp). _
        End(xlUp).End(xlUp).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ElseIf cell.Value = "C" And cell.Offset(0, 3).Value = "X" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
        Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).End(xlUp). _
        End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ElseIf cell.Value = "D" And cell.Offset(0, 3).Value = "X" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
        Sheets("Sheet1").Range("I" & Rows.Count).End(xlUp).End(xlUp). _
        End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ElseIf cell.Value = "E" And cell.Offset(0, 3).Value = "X" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
        Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ElseIf cell.Value = "F" And cell.Offset(0, 3).Value = "X" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
        Sheets("Sheet1").Range("I" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ElseIf cell.Value = "G" And cell.Offset(0, 3).Value = "X" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
        Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    ElseIf cell.Value = "H" And cell.Offset(0, 3).Value = "X" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
        Sheets("Sheet3").Range("K" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End If
Next cell

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

To see the changes I made to your workbook, I'll upload it as well. Note that I changed column C on sheet 4 a bit to have all letters from A to H to test the code and that there is a button with code attached on sheet 4.
http://speedy.sh/nQfVy/Amegolas-Copy-of-Roster-Expirement.xlsm

Best regards,
Trowa

Copy a row from one worksheet to another, based on criteria

2 replies

Subscribe To Our Newsletter!