Loop all Sheets not working.

[Pank]

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.

 

[Mike]

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


For Each wksht In ActiveWorkbook.Worksheets
your code
Next wksht

 

[Pank]

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]

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]

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]

Pank,

Yet another effort. Please try this, tested on Excel 2002.

Sub dontblameme()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Name = ws.Name
Worksheets(Name).Select

Put your code here

Next ws

End Sub

 

[Pank]

Mike,

This worked a treat.

I appreciate your assistance.

 

[Mike]

Your welcome

Mike,

This worked a treat.

I appreciate your assistance.

 

[Roger Govier]

Hi

The problem is that you are not selecting the worksheet after the For
statement.

Also, you can condense your code by carrying out the operations in one
stage as follows, and eliminating many of the Select statements will
make the code run a lot faster.

Sub test()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Select
wks.Range("A1, C1:E1, G1:M1, O1:AL1").EntireColumn.Hidden = True
wks.Range("B1").ColumnWidth = 35
wks.Range("F1").ColumnWidth = 12
wks.Range("N1").ColumnWidth = 20

wks.Rows("1:1").RowHeight = 20
wks.Range("B1, F1, N1").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

Next wks

End Sub

 

[Roger Govier]

My apologies

the line
wks.Range("B1, F1, N1").Select

should of course read
wks.Range("B1, F1, N1").EntireColumn.Select

 

[Don Guillett]

untested but try this without any selections. Your clue could have come from
the with statements in your code.

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets

with ws

.Columns("A,c:e,g:m,o:al").Hidden = True
.Columns("B").ColumnWidth = 35
.Columns("F").ColumnWidth = 12
.Columns("N").ColumnWidth = 20
.Rows("1:1").RowHeight = 20

with .Columns("B")
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
with .Columns("F,N")
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

end with

Next wks
End Sub

 

[Don Guillett]

Columns("A,c:e,g:m,o:al").Hidden = True
should be
range("a1,c1:e1,g1:m1,o1:al1).entirecolumn.hidden=true

with .Columns("F,N")

with .range("f1,n1").entirecolumn

 

[Pank]

Don, Roger,

Thank you for you input, I will try it out.