Just incase anyone on mumsnet is a whizz....
I've got a single worksheet which has several rows of data, each owned by an individual in row A, which I want to copy into separate sheets in the same workbook and call the sheets the individuals name.
The code below works except it copies the 1st row of data (in row A2) as the header for each sheet instead of the actual header (in row A1) itself.
Any ideas?
Private Sub CommandButton1_Click()
Dim wsData As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim LastRowData As Long
Set wsData = ActiveSheet
Set wsCrit = Worksheets.Add
LastRowData = wsData.Range("A" & Rows.Count).End(xlUp).Row
wsData.Range("A2:A" & LastRowData).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
Set rngCrit = wsCrit.Range("A2")
While rngCrit.Value ""
Set wsNew = Worksheets.Add
wsNew.Name = rngCrit
wsData.Range("A2:K" & LastRowData).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit.Offset(-1).Resize(2), CopyToRange:=wsNew.Range("A1"), Unique:=True
rngCrit.EntireRow.Delete
Set rngCrit = wsCrit.Range("A2")
Wend
Application.DisplayAlerts = False
wsCrit.Delete
Columns.AutoFit
ActiveWorkbook.SaveAs Filename:="H:\My Documents\macros" & test & "Members.xls"
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub