VBA code to apply format to all sheets

hblbs

Board Regular
Joined
Mar 18, 2009
Messages
184
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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.

Code:
    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.
 
Upvote 0
This should do the trick

Code:
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
 
Upvote 0
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 ;)
 
Upvote 0
Old thread but is there a way to apply this to all worksheets in a workbook except one? Thanks!
 
Upvote 0
Yeah you'd want to look at something like (air typing here so untested)

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

' Run Code

End If
Next sh
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top