Moving data from one spreadsheet to another on Excel

Closed
heather93 Posts 1 Registration date Wednesday July 23, 2014 Status Member Last seen July 23, 2014 - Jul 23, 2014 at 09:43 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jul 29, 2014 at 11:16 AM
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

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 24, 2014 at 10:33 AM
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
Heather xo 93
Jul 29, 2014 at 04:48 AM
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
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 29, 2014 at 11:16 AM
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