Report

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

Ask a question Amegolas 4Posts Wednesday June 8, 2016Registration date June 14, 2016 Last seen - Last answered on Jun 16, 2016 10:48AM
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. :)
See more 
Helpful
+0
moins plus
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
Amegolas 4Posts Wednesday June 8, 2016Registration date June 14, 2016 Last seen - Jun 13, 2016 01:52PM
Hi TrowaD

It sounds a bit complicated here is the link to the excel

https://www.dropbox.com/s/0tp1yf2a316ucbg/Copy%20of%20Roster%20Expirement.xlsx?dl=0

Sheet 4 is a roster i will be copying and pasting from a separate source (The headers will be the same)
I already have a section that counts the amount of "unexcused" using the countif formula

What i would like is for the colored sections to have the rows on sheet 4 with an x under the unexcused column to be auto populated under their respective managers section on sheet 1 and 3. it doesn't have to be automatic if i could assign a macro to a button would also work.

Thank you again,
Amegolas
Reply
Add comment
Helpful
+0
moins plus
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
Amegolas 4Posts Wednesday June 8, 2016Registration date June 14, 2016 Last seen - Jun 14, 2016 10:00PM
Trowa!


This fantastic Perfect worked amazingly. I appreciate all the time and effort you put into this.

Thank you so much for your time you have been a tremendous help to me!
Reply
TrowaD 2033Posts Sunday September 12, 2010Registration date ModeratorStatus December 6, 2016 Last seen - Jun 16, 2016 10:48AM
Awesome!
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!