Hi Mike,

The code is in Module 5, I also have another macro which is in Module 4. I
note that your version is 2003, my version of Excel is 2002, would that make
any difference?

Once again thank U for your help.


"Mike" wrote:

Hi Pank,

I'm at a bit of a loss to explain this because it works perfectly in my
Excel 2003. Where is the code? it should be a module. Alt + F11 and insert
new module and paste the code in to that. You may get the problem you
describe if yo have right-clicked a sheet tab and pasted the code into that.

Mike

"Pank" wrote:

Mike,

Firstly, thank U for the prompt response.

I put my code round the code you supplied and unfortunately, the result was
the same as previously, in that the code only works on the sheet that is
selected, to run against all other sheets, I have to select them individually
and run.

Any ideas?

"Mike" wrote:

Put this around your code and it will run on all sheets


For Each wksht In ActiveWorkbook.Worksheets
your code
Next wksht

"Pank" wrote:

I have the following macro which I want executed for all worksheets within a
book.

I have tried it out and it only runs in the sheet that is selected, if I
choose another sheet and run it, it runs.

Can someone advise why it is not running against all sheets or what changes
are required to make it run against all sheets?

Sub Formatting()

€˜The following hides columns A,C to E, G to M, and O to AL.

€˜It then sets the column width for Columns B, F and N as well as the €˜row
height for row 1.

€˜Lastly, columns B, F and N are selected and formatting done to €˜ensure that
they are not the Text within the columns is not €˜wrapped.

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Columns("A:A").Select
Selection.EntireColumn.Hidden = True
Columns("C:E").Select
Selection.EntireColumn.Hidden = True
Columns("G:M").Select
Selection.EntireColumn.Hidden = True
Columns("O:AL").Select
Selection.EntireColumn.Hidden = True
Columns("B:B").Select
Selection.ColumnWidth = 35
Columns("F:F").Select
Selection.ColumnWidth = 12
Columns("N:N").Select
Selection.ColumnWidth = 20
Rows("1:1").Select
Range("B1").Activate
Selection.RowHeight = 20
Columns("B:B").Select
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("N:N").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Next wks
End Sub

Any assistance offered is appreciated.