Cut A Row Paste It On First Empty Row In Another Sheet Based on Condition DropDown

[Bookstr]

Hello I am looking for a VBA code to Copy Rows from sheet “Equipment List” to sheet “Shipping Record” when a Validation dropdown is selected to YES in Column N.
It would also clear the contents of that row Column C through N for new entry. Please help!
Thank you

[Bookstr]

I was able to get this to work, but when it transfers to the other sheet a separate row is created containing the worksheet name "Equipment"List

Sub Worksheet_Change(ByVal Target As Range)
Dim strSource
If Not Intersect(Target, Range("N:N")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Target.Value = "YES" Then
        strSource = ActiveSheet.Name
        Range("B" & Target.Row & ":Q" & Target.Row).Copy
       With Sheets("Shipping Record").Range("B" & Rows.Count).End(xlUp).Offset(1)
            .PasteSpecial xlPasteFormats
            .PasteSpecial xlPasteValues
        End With
        Sheets("Shipping Record").Range("B" & Rows.Count).End(xlUp).Offset(1) = strSource
        Application.CutCopyMode = False
        Range("C" & Target.Row & ":Q" & Target.Row).ClearContents
    End If
    Application.EnableEvents = True
End If

Thank you.

[jaslake]

Hi Bookstr

That's happening here

Sheets("Shipping Record").Range("B" & Rows.Count).End(xlUp).Offset(1) = strSource

Where do you want

the worksheet name "Equipment"List

Assuming Column A, try this

Sheets("Shipping Record").Range("B" & Rows.Count).End(xlUp).Offset(0,-1) = strSource

If it doesn't work let me know...I'll look at the File.

[Bookstr]

Thank you John worked like a charm. Is there a way to hide it?

[jaslake]

Hi Bookstr

Regarding this

Is there a way to hide it?

Yes...you could take a couple of approaches:

• You could hide the entire Column
• You could change the Font of that Column to White such that the Column will still be visible but the Text will be White and appear to be unseen.

[Bookstr]

Thank you again. This is now Solved!

[jaslake]

You're welcome...glad I could help.