Copy a row from one worksheet to another, based on criteria
Solved/Closed
Amegolas
TrowaD
- Posts
- 4
- Registration date
- Wednesday June 8, 2016
- Status
- Member
- Last seen
- June 14, 2016
TrowaD
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
Related:
- Copy a row from one worksheet to another, based on criteria
- Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
- How to automatically copy rows in excel to another sheet based on criteria - Guide
- Excel copy rows from one sheet to another based on criteria ✓ - Forum - Excel
- Copy rows from one sheet to another based on criteria ✓ - Forum - Office Software
- Code to move rows into another sheet based on certain values ✓ - Forum - Excel
2 replies
TrowaD
Jun 13, 2016 at 12:04 PM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
Jun 13, 2016 at 12:04 PM
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
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
Jun 14, 2016 at 12:04 PM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
Jun 14, 2016 at 12:04 PM
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 ;).
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
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
Jun 14, 2016 at 10:00 PM
- Posts
- 4
- Registration date
- Wednesday June 8, 2016
- Status
- Member
- Last seen
- June 14, 2016
Jun 14, 2016 at 10:00 PM
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!
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!
TrowaD
Jun 16, 2016 at 10:48 AM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
- Posts
- 4
- Registration date
- Wednesday June 8, 2016
- Status
- Member
- Last seen
- June 14, 2016
Jun 16, 2016 at 10:48 AM
Awesome!
Jun 13, 2016 at 01:52 PM
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