About Us

Pank

Don, Roger,

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

"Don Guillett" wrote:

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
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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
SalesAid Software

"Pank" wrote in message
...
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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Loop through varible list of sheets	[email protected]	Excel Discussion (Misc queries)	2	January 5th 07 08:45 AM
Working with multiple sheets in a workbook	malcolm	Excel Discussion (Misc queries)	1	November 10th 06 02:27 AM
Working with sheets in Excel 2003	Americo	Excel Worksheet Functions	1	July 15th 06 03:48 PM
On Error GoTo Label in a loop only working once.	Ken Johnson	Charts and Charting in Excel	4	July 5th 06 09:39 PM
creating macro working across multiple sheets	Nicole Seibert	Excel Worksheet Functions	8	February 28th 06 05:53 PM

Menu

About Us