Copy row from one worksheet to another based on cell value

[nmccracken12]

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

 

[mumps]

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]

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]

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]

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]

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.

Excel Example.xlsx

Shared with Dropbox

www.dropbox.com

 

[nmccracken12]

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

 

[mumps]

Click here to download your file.

Macro in "Master List" worksheet code module:

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:

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]

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]

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.