Move specific data to new sheet based on chosen dropdown name

[SlyFoxes]

I have an Excel file with headers A1-D1
Data is entered in the first three columns and there is a dropdown in the fourth
I need to copy the detail in columns A-C only to a new sheet of the same name as the dropdown in Column D
I have additional headers on the other named sheets for formula so would need only 3 columns of data to be moved so it does not overwrite the formula
Main Sheet is called "All Receipts"
Dropdown Categories (and corresponding sheets) are Progress / Hold / Complete
I would really appreciate any assistance with VBA code to do this

 

[rpaulson]

try this on a copy of your file.

Sub do_it()
Dim ws As Worksheet

For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row

sht = Cells(r, "D")
If sht = "Progress" Then Set ws = Worksheets("Progress")
If sht = "Hold" Then Set ws = Worksheets("Hold")
If sht = "Complete" Then Set ws = Worksheets("Complete")


wr = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws.Range("A" & wr & ":C" & wr).Value = Range("A" & r & ":C" & r).Value

Next r

End Sub

hth,
Ross

 

[SlyFoxes]

try this on a copy of your file.

Sub do_it()
Dim ws As Worksheet

For r = 2 To Cells(Rows.Count, "D").End(xlUp).Row

sht = Cells(r, "D")
If sht = "Progress" Then Set ws = Worksheets("Progress")
If sht = "Hold" Then Set ws = Worksheets("Hold")
If sht = "Complete" Then Set ws = Worksheets("Complete")


wr = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws.Range("A" & wr & ":C" & wr).Value = Range("A" & r & ":C" & r).Value

Next r

End Sub

hth,
Ross

Ross, thank you so much. This worked absolutely perfectly, you are awesome!

 

[vw412]

what do you see as the trigger to make the copy happen? When copying, should the items be added to the end of the destination sheet?

 

[rpaulson]

you welcome and thanks for the feedback.

vw412

the line below references to the end of the destination sheet.

wr = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1