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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 29, 2014 at 11:16 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 29, 2014 at 11:16 AM
Related:
- Moving data from one spreadsheet to another on Excel
- Transfer data from one excel worksheet to another automatically - Guide
- How to insert picture in word without moving text - Guide
- Tmobile data check - Guide
- Number to words in excel - Guide
- Excel date format dd.mm.yyyy - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jul 24, 2014 at 10:33 AM
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:
Don't forget to change the sheet references in the code.
Best regards,
Trowa
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
Jul 29, 2014 at 04:48 AM
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
Jul 29, 2014 at 11:16 AM
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