Macro to Format Multiple Worksheets

Question

I need a macro to format rows and columns in given worksheets – to work like this:

Worksheet Names: Operations, Staff, Support, [and other worksheets]

Column widths: A = 2.0, B=13.14, C=10.29 [and other columns]

Row heights: 1=12.75, 2=40.5 [and other rows]

Align columns center and vertically: B, C, E [and other columns]

There are multiple worksheets and this macro makes them all look alike, without having to go through each one and set dimensions/formats manually.

Answer 1

Hi,

Because you have option explicit you must dimension all variables so the correction for I is

Dim I as long

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 2

Try the below...Similarly you can have the column width, rowheight details in a similar Array so that you dont need to repeat the lines

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets(Array("Operations", "Staff", "Support"))

    With ws

      .Columns(1).ColumnWidth = 2

      .Columns(2).ColumnWidth = 13.14

      .Columns(3).ColumnWidth = 10.29

      .Rows(1).RowHeight = 12.75

      .Rows(2).RowHeight = 40.5

      .Columns(2).HorizontalAlignment = xlCenter

      .Columns(2).VerticalAlignment = xlCenter

      .Columns(3).HorizontalAlignment = xlCenter

      .Columns(3).VerticalAlignment = xlCenter

      .Columns(5).HorizontalAlignment = xlCenter

      .Columns(5).VerticalAlignment = xlCenter

    End With

Next

  Application.ScreenUpdating = True

Answer 3

Try this macro:

Sub format_worksheets()

  Dim worksheetnames() As Variant

  worksheetnames = Array("Operations", "Staff", "Support") 'and other worksheets

  Application.ScreenUpdating = False

  For i = 0 To UBound(worksheetnames)

    MsgBox worksheetnames(i)

    With Worksheets(worksheetnames(i))

      .Columns("A:A").ColumnWidth = 2#

      .Columns("B:B").ColumnWidth = 13.14

      .Columns("C:C").ColumnWidth = 10.29

    ' and other columns

      .Rows("1:1").RowHeight = 12.75

      .Rows("2:2").RowHeight = 40.5

    ' and other rows

      .Columns("B:B").HorizontalAlignment = xlCenter

      .Columns("B:B").VerticalAlignment = xlCenter

      .Columns("C:C").HorizontalAlignment = xlCenter

      .Columns("C:C").VerticalAlignment = xlCenter

      .Columns("E:E").HorizontalAlignment = xlCenter

      .Columns("E:E").VerticalAlignment = xlCenter

    ' and other columns

    End With

  Next i

  Application.ScreenUpdating = True

End Sub

Hope this helps / Lars-Åke

Answer 4

Mike,

Thanks for the help.  This macro works as expected; however, it only works for the current worksheet.  I'm trying to make it work for all worksheets at one time whether current or not.

Phil

Answer 5

This code is installed in Module 1 of the workbook, which has Option Explicit. As a result, “Compile error: Variable not defined” results when the macro is run.

Highlighted lines:

“Sub Format…..” highlighted yellow.

“For I = ….” Line, the “I” is highlighted.

What is the code correction?