Copy row from one worksheet to another based on cell value

nmccracken12

New Member
Joined
Oct 8, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hopefully someone can help me with this.. I have been googling for hours. I have a sheet named Master Import List. I would like to copy only certain cells from this sheet to another inside the workbook based on the cell value. Here is the layout.

Sheet 1 = Master Import List
Sheet 2 = Items to update

Cell Values - in column Z
Done = do not copy to Items to update sheet and if this item is on that sheet remove it. Hopefully that makes sense
Quote = Copy certain cells from Master Import List to Items to update and Highlight that row red. Delete from Items to Update sheet when value changed to DONE
Update = Copy certain cells from Master Import List to Items to update and Highlight that row yellow. Delete from Items to Update sheet when value changed to DONE
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,745
It would be more helpful if you were more specific in describing which cells you want to copy and where you want them pasted. The best way to do this is to use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of both sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 

nmccracken12

New Member
Joined
Oct 8, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply. I think I am going to change the way I want to do this. Instead of trying to explain it in deep detail though, I will simply ask first if these things can be done, before using anyone's time. This is what I am visualizing and I am still google searching myself to try and do some of this. On my Master Import List, I have a few thousand rows and columns A-Z. I have a form that has to be filled out. Is it possible to create a checkbox or cell value, that tells this form which row to pull the certain cells from.

For example... I am trying to fill the form with information from row 350. I would need info from 350 A,B,C,etc... to populate in the form. The next time I might need info from row 1350... Please bare with me. I am trying my best to do this on my own, so I apologize if what I am saying makes no sense.
 

nmccracken12

New Member
Joined
Oct 8, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I have most all of this sorted. The only question I have is. My master list has a UPC and SCC code on it and its all on one line. The form I am filling in, separates them into 2 different rows. 6 on one row and 6 on the next. Is there a way to tell VLOOKUP to do this?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,745

ADVERTISEMENT

VLookUp can only return a value. You will need a macro. If you need help, please follow the instructions in Post #2 and I will see what I can do.
 

nmccracken12

New Member
Joined
Oct 8, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi! Thanks again for the help. Here is a small piece of the file that I am needing this to work on. I can give you more info if you need it.

 

nmccracken12

New Member
Joined
Oct 8, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The above is going back to the original question of this thread.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,745
Click here to download your file.

Macro in "Master List" worksheet code module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Intersect(Target, Range("BC:BC")) Is Nothing Then Exit Sub
    Dim lCol As Long
    Select Case Target.Value
        Case "Quote"
            With Sheets("Items to Update")
                lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
                Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                .Cells(.Rows.Count, "A").End(xlUp).Resize(, lCol).Interior.ColorIndex = 3
            End With
        Case "Update"
            With Sheets("Items to Update")
                lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
                Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                .Cells(.Rows.Count, "A").End(xlUp).Resize(, lCol).Interior.ColorIndex = 6
            End With
    End Select
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Macro in "Items to Update" worksheet code module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Intersect(Target, Range("BC:BC")) Is Nothing Then Exit Sub
    Dim fnd As Range
    If Target = "Done" Then
        Set fnd = Sheets("Master List").Range("A:A").Find(Range("A" & Target.Row).Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            Sheets("Master List").Range("BC" & fnd.Row) = "Done"
            Target.EntireRow.Delete
        End If
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub
 

nmccracken12

New Member
Joined
Oct 8, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
This is awesome!! The only issue I am having is when I moved those into my full workbook, it only colors the first column on the second sheet. It does not color the entire row all the way across. Any ideas? Thank you again! I could make that work if it comes to it.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,745
You are very welcome. :) Make sure that on the second sheet in your full workbook, row 1 contains all the headers as in the workbook that I attached in Post #8.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,509
Messages
5,548,486
Members
410,839
Latest member
MrSumo85
Top