Auto copy data

[UniAmyColeman]

Hi All

I am useless with macros etc...but I'd like to be able to do this....

I have a spreadsheet that contains a master set of data on worksheet "All Students" - simple with only 4 columns. Column D contains the name of the Progress Coach responsible for the student detailed in columns A-C.

I have created a tab for each person responsible x 10 - Caroline, Dave, Jo, Kayleigh, Lisa, Pam, Rachael, Sheridan, Stacey and Tracy.

Each tab has the same column headings
A - Surname
B - Forename
C - Pathway
D - Progress Coach

I would like the spreadsheet to be able to copy data from columns A-C to each Progress Coach named tab where their name appears in column D.

Is this achievable?

Thank you!
Amy

 

[Fluff]

Do you want this to happen whenever you enter a value in col D, or do you want to decide when to run it?

 

[UniAmyColeman]

Either is fine! I don't mind if it does it when I shut the workbook via a prompt. Doesn't have to be immediate of that makes it trickier? Thank you ??

 

[shadbolt]

The way I have done this before is on tab 2, cell a1 =a1 from tab 1. Drag to the side and down so you have a copy of the original tab. Then put a filter on that tab to only show that progress coach. Repeat for other tabs. Small issue is that its not automatic refreshing.

 

[Fluff]

Ok how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 4 Then
      If Evaluate("isref('" & Target.Value & "'!A1)") Then
         Intersect(Target.EntireRow, Range("A:C")).Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
      Else
         MsgBox "Sheet " & Target.Value & " does not exist"
      End If
   End If
End Sub

This needs to go in the All Students sheet module and will copy each row when a value in col D is changed.