Formatting Multiple Sheets

[Lovelylou79]

Hi Again VBA Experts,

I am trying to select all sheets in my workbook after and inclusive of Sheets("ps"), then format each sheet.

I have the following code. It selects all sheets, all sheets widen to 100, but only the first sheet (ps) is then reformatted to Autofit.

Can you please point out where my code is failing. It seems to work during a step through.

Sub FormatMySheets()

Dim i As Long

'Select All sheets
Sheets("ps").Activate
For i = ActiveSheet.Index To Sheets.Count
Sheets(i).Select Replace:=False

Cells.Select

With Selection
.ColumnWidth = 100
.EntireColumn.AutoFit
.EntireRow.AutoFit

End With

Next i

End Sub

Thank you

 

[JoeMo]

Not sure what you mean by "It seems to work during a step through. ", but I think you will have to loop through each sheet for the autofit lines.

 

[Logit]

.
Just tested your macro here ... it works as designed on my machine.

This macro works as well :

Option Explicit


Sub FormatMySheets()


Dim i As Long


'Select All sheets
Sheets("Sheet1").Activate
For i = 1 To Sheets.Count  '<-- I changed this line only
Sheets(i).Select Replace:=False


Cells.Select


With Selection
.ColumnWidth = 100
.EntireColumn.AutoFit
.EntireRow.AutoFit


End With


Next i


End Sub

 

[tonyyy]

Lovelylou79,

Welcome to the Board.

You might consider the following...

Sub FormatMySheets()
    Dim i As Long
    Sheets("ps").Activate
    For i = ActiveSheet.Index To Sheets.Count
        With Sheets(i).UsedRange
            .ColumnWidth = 100
            .EntireColumn.AutoFit
            .EntireRow.AutoFit
        End With
    Next i
End Sub

Cheers,

tonyyy

 

[Lovelylou79]

Thank you for your responses. All very helpful.
Issue resolved!