Formating Cells of Multi-Worksheets

  • Thread starter Thread starter tomwesnick
  • Start date Start date
T

tomwesnick

I have a workbook with over 17 worksheets. Is there an easier way to
format all the cells in every worksheet of a workbook the identical
way-- ie Arial Narrow 10pt. I got this code from a website, (But this
only formats the footer) I would like to format all the cells. Any
help would be appreciated.

Sub InsertHeaderFooter()
' inserts the same header/footer in all worksheets
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing header/footer in " & ws.Name
With ws.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial Narrow,Regular""&F &A"
.CenterFooter = ""
.RightFooter = "&""Arial Narrow,Regular""&D &T"
End With
Next ws
Set ws = Nothing
Application.StatusBar = False

End Sub
 
You could record a macro when you do one worksheet manually.

Select the cells (ctrl-a (twice in xl2003)) and apply the format:

Then generalize it and delete the stuff you don't want:

Option Explicit
Sub Macro1()

Cells.Select
With Selection.Font
.Name = "Arial Narrow"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub

becomes...

Option Explicit
Sub ReformatCells()

Dim Wks as worksheet
for each wks in activeworkbook.worksheets
with wks.cells.Font
.name = "Arial Narrow"
.Size = 10
end with
next wks

End Sub
 
tomwesnick said:
I have a workbook with over 17 worksheets. Is there an easier way to
format all the cells in every worksheet of a workbook the identical
way-- ie Arial Narrow 10pt. I got this code from a website, (But this
only formats the footer) I would like to format all the cells. Any
help would be appreciated.

Sub InsertHeaderFooter()
' inserts the same header/footer in all worksheets
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Changing header/footer in " & ws.Name
With ws.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial Narrow,Regular""&F &A"
.CenterFooter = ""
.RightFooter = "&""Arial Narrow,Regular""&D &T"
End With
Next ws
Set ws = Nothing
Application.StatusBar = False

End Sub
Click to expand...

Try something like this

Sub FormatSheets()
For ws = 1 To 17
Sheets(ws).Select
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 10
End With
Range("A1").Select
Next ws
End Sub
 
Without code......

Right-click on a sheet tab and "Select all sheets".

Hit CRTL + A on active sheet.

Format as you wish. What you do to one sheet will be done to all.

!!DO NOT FORGET to ungroup the sheets after!!

Forgetting can lead to disastrous results.

Gord Dibben Excel MVP
 
Thank-you everyboby, from saving me the headache of formating each
sheet. Solutions work like a charm.

Tom
 
Back
Top