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 wrote:
> 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
>

[Craig]

that worked perfectly, thanks a lot