Copy a row from one worksheet to another, based on criteria [Solved/Closed]
Amegolas4
Posts Wednesday June 8, 2016Registration date
June 14, 2016
Last seen
-
Jun 8, 2016 at 09:47 AM - Latest reply: TrowaD2340
Posts Sunday September 12, 2010Registration dateModeratorStatus
April 19, 2018
Last seen
- Jun 16, 2016 at 10:48 AM
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. :)
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.
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.
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
TrowaD2340
Posts Sunday September 12, 2010Registration dateModeratorStatus
April 19, 2018
Last seen
>
Amegolas4
Posts Wednesday June 8, 2016Registration date
June 14, 2016
Last seen -Jun 16, 2016 at 10:48 AM
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