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" wrote:

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

Pank Mehta wrote:

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