Sub t()
Dim ary As Variant, i As Long, sh As Worksheet
ary = Array("Taranaki", "Wellington", "Bay Of Plenty", "Auckland", "Manawatu", "Gisborne", "Wairarapa")
With Sheets("DATA")
For i = LBound(ary) To UBound(ary)
Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
sh.Name = ary(i)
.UsedRange.AutoFilter 7, ary(i)
.UsedRange.SpecialCells(xlCellTypeVisible).Copy sh.Range("A1")
.AutoFilterMode = False
Next
End With
End Sub
Install this in code module1. You can run it from the Excel window with Alt + F8, then use the dialog box for macros.
Bookmarks