create new sheets from a header row

[Craig]

I'm trying to take a sheet of compiled data and create several sheets
of individual data. The headers are in column A, so i'm trying to
create a new sheet in the workbood for each value of column A. It
keeps bombing out at different points, depending on what i try, here's
what i have so far, the values in colum A start at A6, it's flagging
For Each C In MyRange right now, any ideas?

Sub NewSheet()
Dim MyRange As Range
Dim C As Range
Dim NewSheetName As String
Range("A6").Select
ActiveCell.End(xlDown).Select
xLastRow = ActiveCell.Row
Range("A6" & xLastRow).Name = "MyRange"

For Each C In MyRange
Range("A" & MyRange).Name = "C"
C.Select
NewSheetName = Selection.Value
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move After:=Worksheets(Worksheets.Count)
.Name = NewSheetName
End With
Next C

End Sub

 

[Rowan Drummond]

Try:

Sub newSheet()
Dim MyRange As Range
Dim C As Range
Dim NewSheetName As String
Dim newSheet As Worksheet
Dim xLastRow As Long

xLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A6:A" & xLastRow)

For Each C In MyRange
NewSheetName = C.Value
Set newSheet = Sheets.Add
With newSheet
.Move After:=Worksheets(Worksheets.Count)
On Error Resume Next
.Name = NewSheetName
On Error GoTo 0
End With
Next C

End Sub

Hope this helps
Rowan

 

[Craig]

that worked perfectly, thanks a lot