I was just reading a solution explained by our member Saqib Husain, Syed in the thread https://www.experts-exchange.com/questions/28654243/Copy-data-from-Main-Sheet-to-multiple-sheets-based-on-a-criteria.html
I have a similar situation with excel 2013. I already sent a message to Syed with my request but not sure whether Syed is still active in this forum. Would anyone be able to help?
I would like to have a Macro in the ‘Task Allocation’ sheet to copy the rows from Column B to Column G and paste it to the individual ‘Team WiP’ sheet as per selection from the drop down list from column A in Sheet-‘Task Allocation’.
The selection of rows starts from row 6 only as row # 5 is the header
The entries in the ‘Task Allocation’ sheet need to be cut and copied across to the relevant sheets as per selection from column A.
As an example, if I select ‘Team 1’ from the dropdown list, the entire data on that row from column B to G need to be copied in to ‘Team1 WiP’ sheet. If there are more allocation to Team 1 from the Task allocation sheet, it would be copied to the next available row in the individual team sheet. Similarly, If I select Team 2, then the data will be copied to ‘Team2 WiP’ sheet. I have 6 teams to select from.
I have attached my sample file and the code I have which is not working is pasted below
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A6:A" & Range("B" & Rows.Count).End(xlUp).Row)) Is Nothing Then Exit Sub
Range("B" & Target.Row & ":G" & Target.Row).Copy Sheets(Target.Value).Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
Thanks in advance