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

Question

Previous Topic Next Topic

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

Report

Amegolas

Posts: 4
Registration date: Wednesday June 8, 2016
Status: Member
Last seen: June 14, 2016

- Jun 8, 2016 at 09:47 AM
TrowaD

Posts: 2755
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 3, 2021

- 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. :)

Copy a row from one worksheet to another, based on criteria
Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
Excel copy rows from one sheet to another based on criteria ✓ - Forum - Excel
How to copy an entire row to another worksheet based on cell value in excel ✓ - Forum - Excel
Copy paste special into last row of another worksheet ✓ - Forum - Excel
Auto-copy of select rows to another worksheet ✓ - Forum - Excel

2 replies

Ask a question

TrowaD · Answer 1 · 2016-06-13T12:04:46-0400

Answer 1 / 2

TrowaD

Posts: 2755
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: June 3, 2021

462
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

Report

Amegolas

Posts: 4
Registration date: Wednesday June 8, 2016
Status: Member
Last seen: June 14, 2016

Jun 13, 2016 at 01:52 PM

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

TrowaD · Answer 2 · 2016-06-14T12:04:48-0400

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

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!

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

2 replies

Your reply

Subscribe To Our Newsletter!