Extracting rows to new tabs by column VBA

[NJPov]

Hi everyone,

I'm an educator who is having trouble with some code to write for a task I need to do. I have a spreadsheet with column headings and a first row of data as such:

A B C D E F G H I J K L M N O P Q R S
Child Gender DOB Age Address Form Class CAT Attendance Behaviour Other Maths English Science History Geography Art Phys.Ed Spanish French
A.N. Other M 22.2.12 7 xxx 4P1 95 88 Medium None B B C C C C B A B

I would like to know if its possible to run a macro which would automatically give me new tabs, but have those tab headings be the Form Class (row E) and not child name (row A), and thus populate that tab for all pupils from a certain form. Also would it be possible to highlight the entire cell based on category of behaviour? So red for Poor, Amber for Medium and Green for Good?

I have managed to put some code in but my tabs keep dividing by name and not form class as I would like.

Any help would be greatly appreciated.

Neil

 

[Fluff]

How about

Sub NJPov()
   Dim Ws As Worksheet
   Dim Cl As Range
   Dim Ky As Variant
   
   Set Ws = ActiveSheet
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("F2", Ws.Range("F" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Ky In .Keys
         Sheets.Add(, Sheets(1)).Name = Ky
         Ws.Range("A1:S1").AutoFilter 6, Ky
         Ws.AutoFilter.Range.Copy Range("A1")
      Next Ky
   End With
   Ws.AutoFilterMode = False
End Sub

 

[NJPov]

Worked a treat - thank you so much, saved me loads of time

 

[Fluff]

You're welcome & thanks for the feedback