Hi All,

Searched and searched and couldn't find anything. Could you please help.

I've got this code below which looks at my data and when data changes in the row it grabs it all and adds a new sheet and pastes the data. Problem I have now is I want to format each sheet (the same way) but I don't know the sheet names so can't reference them. Is there anyway I can loop through uknown names and perform a code?

Dim lastrow As Long, LastCol As Integer, I As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("A1"), Order1:=xlAscending, _
    header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For I = 2 To lastrow
    If .Range("A" & I).Value <> .Range("A" & I + 1).Value Then
        iEnd = I
        Sheets.Add after:=Sheets(Sheets.Count)
        Set ws = ActiveSheet
        On Error Resume Next
        ws.Name = .Range("A" & iStart).Value
        On Error GoTo 0
        ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
        .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy
        ws.Range("A2").Select
        ActiveSheet.Paste
        Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
        iStart = iEnd + 1
    End If
    Next I
End With
I've tried this code but it only seems to do it on the first sheet - but does it at the count of the other sheets.

Dim z As Integer
Dim lrow As Long

For z = 1 To Sheets.Count
    With Worksheets(z)
        Rows("1:2").Select
    Selection.Insert Shift:=xlDown
    Range("A1:K1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.merge
    End With
    Next z
Any help greatly appreciated