Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
My file has a main worksheet that I want rows to automatically copy to other worksheets depending on the drop down list choice. The column with the drop down list has multiple choice example 'snow' 'call in' 'meeting' and I want if the option 'snow' is listed, I want that row of data to automatically copy to the 'snow' worksheet. Any/all help is very much appreciated.
Thank you.
Re: Copy row to another worksheet depending on drop down list choice.
In which cell is the drop down list located? Are there multiple rows that contain the word "snow" and if so what column is the word in? It would be easier to help if you could post a copy of your file with a detailed explanation of what you want to do.
Re: Copy row to another worksheet depending on drop down list choice.
Quote from Mumps;701038In which cell is the drop down list located? Are there multiple rows that contain the word "snow" and if so what column is the word in? It would be easier to help if you could post a copy of your file with a detailed explanation of what you want to do.
Hoping the file attached (first time user). There will be many rows for each option. If you can see my spreadsheet column G has a drop box of 10 options, I would like each row to copy to the corresponding worksheet.
Re: Copy row to another worksheet depending on drop down list choice.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet "DATA SHEET" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selection in column G.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name <> "DATA SHEET" Then
If ws.Name = Target Then
Target.EntireRow.Copy Sheets(ws.Name).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
End If
Next ws
Application.ScreenUpdating = True
End SubRe: Copy row to another worksheet depending on drop down list choice.
Thanks so much for that, the only problem I am finding is that the only thing that seems to carry over to the other sheet is column g and I need all of the row. Any thoughts?
Re: Copy row to another worksheet depending on drop down list choice.
Cancel that. You...are...a...genius! It works. I can't thank you enough!
Hate to ask more, but is there a way for me to also have the main sheet move the row to the corresponding sheet according to column B as well?
Re: Copy row to another worksheet depending on drop down list choice.
Do you have existing sheets for each name in the drop down in column B? If you do, could you please post that file?
Re: Copy row to another worksheet depending on drop down list choice.
They are both in the original file I posted. This reply box doesn't seem to let me add it again.
Re: Copy row to another worksheet depending on drop down list choice.
Try this macro. Make sure that you enter all your data in the other columns first and make the selection in column G last because the selection in column G runs the macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim rng As Range
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name <> "DATA SHEET" Then
If ws.Name = Target Then
Target.EntireRow.Copy Sheets(ws.Name).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
End If
Next ws
For Each ws In Sheets
For Each rng In Range("B3:B" & LastRow)
If ws.Name = rng Then
rng.EntireRow.Copy Sheets(ws.Name).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next rng
Next ws
Application.ScreenUpdating = True
End SubRe: Copy row to another worksheet depending on drop down list choice.
It worked and it didn't. Now there are rows on the 'reason' pages that have no 'person' and the rows appear on the 'person' pages but show multiple times. Feels like it we are almost there...
Re: Copy row to another worksheet depending on drop down list choice.
I tried the macro on the file you posted and it worked for me. You must make sure that you first fill in all the columns except column G in any particular row. Leave the selection in column G until last.
Re: Copy row to another worksheet depending on drop down list choice.
I just tried it again and only entered two lines of data. The row copies to the 'person' worksheet but lists it twice.
Re: Copy row to another worksheet depending on drop down list choice.
I tried with more lines of data... I entered 7 rows of data on the main sheet and it for the first 'person' I used, it shows up 7 times on that 'person' sheet, for the second 'person' I used, it shows up 6 times on that 'person' sheet, for the third 'person' I used, it shows up 5 times on that 'person' sheet, so on and so forth.
Re: Copy row to another worksheet depending on drop down list choice.
Could you post a copy of that file?
Re: Copy row to another worksheet depending on drop down list choice.
I have been trying to, but can't seem to find that option. Anyway I can email it to you directly?
Re: Copy row to another worksheet depending on drop down list choice.
Click 'Reply to Thread' and then click the 'Go Advanced" button in the lower right. Then click the paperclip icon in the menu to add an attachment.
Re: Copy row to another worksheet depending on drop down list choice.
Hope it worked.
Re: Copy row to another worksheet depending on drop down list choice.
I tried the file and I believe that it is working properly unless I didn't understand how you actually want it to work. This is how the code works: It works one row at a time. For example, let's start in row 3. You enter a date in column A, select a person in column B, proceed to complete columns C to F, then column H and leave column G for last. Row 3 will be copied to the sheet with the person's name you selected in column B and to the sheet whose name you selected in column G. It copies only one row at a time, in this case row 3. Now you go to row 4 and enter the data in the same fashion. If you choose the same name in column B, row 4 as in column B, row 3, then row 4 will be added to the sheet named after that person, so it now will have 2 entries, one from row 3 and one from row 4. Then the process is repeated. Is this not how you wanted it to work?
Re: Copy row to another worksheet depending on drop down list choice.
If row one is Joe and snow, I want that row to appear on the Joe sheet and the snow sheet.
Then row two is John and meeting, I want that row to appear on the John sheet and the meeting sheet.
Then row three is Joe and phone, I want that to appear on the Joe and the phone sheet.
Then row four is John and snow, I want that row to appear on the John sheet and the snow sheet.
So the result would be:
main page: two rows with Joe and two rows with John
Joe page: two rows showing snow and phone
John page: two rows showing meeting and snow
Snow page: two rows showing Joe and John
Meeting page: one row showing John
Phone page: one row showing Joe
But what I am getting is:
main page: two rows with Joe and two rows with John
Joe page: five rows with snow, three rows with phone
John page: five rows with meeting and one row with snow
Snow page: correct with the right rows, one Joe and one John
Meeting page: John's row showing twice
Phone page: correct with the right row, Joe once
Attached the outcome.
Re: Copy row to another worksheet depending on drop down list choice.
I think that I've found the problem. Try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim rng As Range
Dim LastRow As Long
LastRow = Sheets("DATA SHEET").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim ws As Worksheet
For Each ws In Sheets
If ws.Name <> "DATA SHEET" Then
If ws.Name = Target Then
Target.EntireRow.Copy Sheets(ws.Name).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
End If
Next ws
For Each ws In Sheets
If ws.Name = Cells(Target.Row, "B") Then
Target.EntireRow.Copy Sheets(ws.Name).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
Next ws
Application.ScreenUpdating = True
End SubDon’t have an account yet? Register yourself now and be a part of our community!