Grouped Sheets and Formating

[Guest]

I recorded the following macro to undertake formatting of certain columns to
autofit and centre certain columns.

Columns("A:A").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Range("K1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With

Columns("C:C").Select
With Selection
.HorizontalAlignment = xlGeneral
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlGeneral
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Prior to the start of the macro the following line has been inserted
“Worksheets.Select†to enable all sheets in the workbook to be selected (i.e.
grouped).

However, once the macro has run none of the formatting has taken affect.

I then tried to do the formatting manually (again by grouping all sheets)
and it works.

Any suggestions as to why the macro doesn’t work would be appreciated.

Any help to streamline the macro would be appreciated.

 

[Dave Peterson]

Some code works with grouped sheets--some doesn't. I didn't try your code
against grouped sheets (but I trust your results).

And when you record code, you may want to delete the steps you did that were in
error--or toss that recorded macro and record again (trying not to make false
steps).

This cycles through each of the worksheets in the workbook:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("a:a,c:c,k:k").EntireColumn.AutoFit
With .Range("K1")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Range("c:c,f:g")
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
End With
Next wks
End Sub

 

[Guest]

Dave,

Once again your help has been invaluable.

The reason why I had what seemed repeat steps was that when I depressed the
Centre button, it only seemed to centre the heading, pressing it again
centred all the data within the column.

As an extension, using a macro how do I set the height of say rows 1 to nn?

Thanks again

 

[Dave Peterson]

Autofit the rows???

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim N As Long
N = 123 'whatever you used.

For Each wks In ActiveWorkbook.Worksheets
With wks
.Rows("1:" & N).AutoFit
End With
Next wks
End Sub

I like to use just the usedrange.

Option Explicit
Sub testme2()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
.usedrange.rows.AutoFit
End With
Next wks
End Sub