VBA code to apply format to all sheets

[hblbs]

Hi

I was wondering if anyone knew what code can be used to apply formats to all sheets in a workbook. For example, in each sheet I have to apply filters to column A:E, Auto fit columns and Freeze windows at Row 2. I do this manually to all sheets and there can be up to 30 sheets. Anyone know how this can be done in one go?

Thanks in advance.

 

[dave3009]

Hi

One option would be to click you first sheet, hold down the shift key then click your last you will note all the sheet tabs change colour.

Now whatever you apply to the first sheet will apply to all

If you still want code then it's probably best to post the code that the macro recorder gives you so it can be adjusted

KR


Dave

 

[hblbs]

Hi thanks for the reply, below I've recorded what you have advised. However only the Column AutoFit applies when the sheets are selected manually, the Autofilters and Freezing rows below row 2 do not apply. I was wondering is there any code which selects all sheets in a workbook and applies the format to the entire workbook.

    Range("A1").Select
    Sheets(Array("Sheet1", "CTDISER02_", "CTDISER01_", "UNAPPCHG", "ASCLMNT", _
        "INCOMPLCL", "INSTERR10")).Select
    Sheets("Sheet1").Activate
    Columns("A:E").Select
    Selection.Columns.AutoFit
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Range("A1").Select

Note that the sheets named here are not complete and can change all the time.

 

[dave3009]

This should do the trick

Sub Applyformats()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
    sh.Activate
    Columns("A:E").EntireColumn.AutoFit
    Rows("2:2").Activate
    ActiveWindow.FreezePanes = True
Next sh
End Sub

 

[hblbs]

ADVERTISEMENT

Thanks for this, it works wonderfully. Is there a way to Auto filter as well?

 

[dave3009]

Your recorded macro didn't ask for an autofilter but just apply the filter using the recorder and paste it before the Next sh bit

 

[hblbs]

ADVERTISEMENT

Nice one, thanks for your help and your prompt response.

Cheers

 

[dave3009]

Glad to help

 

[Legacy 320057]

Old thread but is there a way to apply this to all worksheets in a workbook except one? Thanks!

 

[dave3009]

Yeah you'd want to look at something like (air typing here so untested)

For each sh in ActiveWorkbook.Sheets
If sh.name <> "IgnoredSheet" Then

' Run Code

End If
Next sh