Looping through unkown sheet names

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

Thanks for the reply daffodil - but it didn't work either, does the same as my other code. Just does it for the first sheet. At the moment I've got 9 different sheets and it does it 9 times on the first sheet.

Any other thoughts?

Sorry daffodil - pinkponies didn't work :P

failed at this

ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value

I'm not having any problems with this part of the code though. Once this part finishes its all good, it's the next part when I call

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

It is in a module. I will try that code when I get back in the office, thanks

Also both the codes I use are in different subs, I use another to call them.

The bit that you have at the end of post #8 does not reference the worksheet(z) when selectin Range("A1:K1") so it will always reference the activesheet rather than looping through them. Try something like:

Sub macro_2()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    ws.Rows("1:2").Insert shift:=xlDown
    With ws.Range("A1:K1")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Merge
    End With
Next ws
End Sub

Thanks all for replying and helping me, I started at the bottom with Norie's and it worked a charm
Cheers!!

Yes mate, and the . Before the rows to, also made some other changes, just more formatting really. Thanks