Moving data from one spreadsheet to another on Excel

Closed
Report
Posts
1
Registration date
Wednesday July 23, 2014
Status
Member
Last seen
July 23, 2014
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
I really need to be able to set up a macro & formula so that once I pick an option from a drop down list on my work book, that will automatically place that row in another sheet, this will be named the same as the drop down option and I need 4 options, on 4 sheets.

Can anyone help with the formula for this?

Thanks

1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Heather,

Let's assume that your drop down list is in column A (2nd code line), then right-click the sheet's tab, select "View code" and paste the following code to the big white field that appears:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub

If Target = "Sheet1" Then
    Target.EntireRow.Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
ElseIf Target = "Sheet2" Then
    Target.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
ElseIf Target = "Sheet3" Then
    Target.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
ElseIf Target = "Sheet4" Then
    Target.EntireRow.Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
End If
End Sub


Don't forget to change the sheet references in the code.

Best regards,
Trowa
0

Thank you so much for this !

This is my finished code based on what you gave me

rivate Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("U:U")) Is Nothing Then Exit Sub

If Target = "Waiting on costs" Then
Target.EntireRow.Copy Sheets("Waiting on costs").Range("U" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target = "Rejected Claims" Then
Target.EntireRow.Copy Sheets("Rejected Claims").Range("U" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target = "Pass to Brand" Then
Target.EntireRow.Copy Sheets("Pass to Brand").Range("U" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target = "Customer to Pay" Then
Target.EntireRow.Copy Sheets("Customer to Pay").Range("U" & Rows.Count).End(xlUp).Offset(1, 0)

ElseIf Target = "Customer Paid" Then
Target.EntireRow.Copy Sheets("Customer Paid").Range("U" & Rows.Count).End(xlUp).Offset(1, 0)


End If
End Sub

My drop down list on column U consists of the 5 sheets I have of which I need the date to go to - am I doing something wrong as it seems that pick ing an option dioes not copy it to another sheet?

Thanks again

Heather
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Heather,

If nothing is happening, then you might have pasted the code in the wrong spot. Remember to place it under the right sheet and not in a module.

Secondly I noticed that you used column U as destination. I don't know if you thought you had to change all the A's into U's or that you really want it there.
So the second code line refers to the column with the drop down lists. (Target, Columns("U:U"))

The 5th, 8th, 11th etc. code line, the part Range("U" & Rows.Count) refers to the column the data will be pasted to based on the first available row (from the bottom up). So if you want it in U4 but you also have data U100 then you will find the data in U101.

Lastly make sure your phrases in the code matches those of your dropdown list, including any capitals. So selecting "Waiting on Costs" from the dropdown list will not trigger the code, because you didn't use a capitol C in the code.

Did any of this help?

Best regards,
Trowa
0