Running Macro on every sheet in Workbook !!!

[Jako]

I have a Sub called ColHidden() which i want to run on every workshee
in Workbook "ClearOrClosedSent.xls".
The Worksheets will have different names however.

Can anyone please tell me how to apply the Sub on all worksheets in th
afforementioned workbook?

I am using Excel 2000.

TI

 

[Frank Kabel]

Hi
try something like

sub all_sheets()
dim wks as worksheet
for each wks in worksheets
wks.column(1).hidden=true
next
end sub

 

[Jako]

Thanks for the reply.

Trouble is i want to Hide various columns A,F,K,L etc
and also i want to possibly add Column headings so i really want th
flexibility of just calling a Subroutine as mentioned to run on ever
sheet, but to be able to chop and change to the Subroutine itself.

Any ideas??

Thanks agai

 

[Tom Ogilvy]

sub all_sheets()
dim wks as worksheet
for each wks in worksheets
ChopAndChange wks
next
end sub

Sub ChopAndChange(wks as Worksheet)
wks.Columns("A:A,F:F,K:L").Hidden = True
wks.Cells(1,2).Resize(1,4).Value =Array("HeaderB", _
"HeaderC","HeaderD","HeaderE")
End sub

 

[Jako]

Fantastic..

Thanks again Tom

 

[Jako]

OOoooopps!!

Spoke to soon Tom.

wks.Columns("A:A,F:F,K:L").Hidden = True

comes up with Type mismatch error !!

 

[Vasant Nanavati]

Try:

wks.Range("A:A,F:F,K:L").EntireColumn.Hidden = True

 

[Tom Ogilvy]

Try it like this:

wks.Range("A:A,F:F,K:L").EntireColumn.Hidden = True

 

[Jako]

Thanks Tom but i'm a bit confused by this code:

wks.Cells(1,2).Resize(1,4).Value =Array("HeaderB", _
"HeaderC","HeaderD","HeaderE")

I have sorted the columns that i want to Hide but then i want to Inser
a column into "A" (so it is the first column on the worksheet) calle
"Heading 1" then insert from column W - Z
headings "Headig 2", "Heading 3", "Heading 4" and "Heading 5" all i
bold font.
After this i then want to make all cells on the worksheet as Tex
format.

Please advise

Many thank

 

[Tom Ogilvy]

wks.Columns(1).Insert
With wks.Cells(1,1)
.Value = "Heading 1"
.font.Bold = True
End With
with Range("W1").Resize(1,4)
.Value = Array( _
"Heading 2", "Heading 3", _
"Heading 4", "Heading 5")
.font.Bold = True
End With

Cells.Interior.NumberFormat = "@"

 

[Jako]

Tom,
Thanks again but only the first worksheet is formatted as i need.
Here is the code i now have:



Sub all_sheets()
Dim wks As Worksheet
For Each wks In Worksheets
ChopAndChange wks
Next
End Sub

Sub ChopAndChange(wks As Worksheet)
wks.Range("C:C,G:G,I:I,K:M,P:R").EntireColumn.Hidden = True
wks.Columns(1).Insert
With wks.Cells(1, 1)
.Value = "Heading 1"
.Font.Bold = True
End With
With Range("W1").Resize(1, 4)
.Value = Array( _
"Heading 2", "Heading 3", _
"Heading 4", "Heading 5")
.Font.Bold = True
End With


Sheets 2 & 3 have the 1st columns + "Heading 1" but not the
W - Z column changes.

TI

 

[Charles]

Jako,

Not realy sure, but change:

For Each wks In Worksheets

to

For Each wks In Workbook

HTH

Charle

 

[Jako]

Thanks Charles but that comes up with an error !!

I have come up with this code that does exactly what i want but i
anyone can optimise it i would be most grateful.



Sub all_sheets()
Dim wks As Worksheet
For Each wks In Worksheets
'For Each wks In Workbook
ChopAndChange wks
Next
End Sub

Sub ChopAndChange(wks As Worksheet)
wks.Range("C:C,G:G,I:I,K:M,P:R").EntireColumn.Hidden = True
wks.Columns(1).Insert
With wks.Cells(1, 1)
.Value = "Heading 1"
.Font.Bold = True
End With
With wks.Cells(1, 23)
.Value = "Heading 2"
.Font.Bold = True
End With
With wks.Cells(1, 24)
.Value = "Heading 3"
.Font.Bold = True
End With
With wks.Cells(1, 25)
.Value = "Heading 4"
.Font.Bold = True
End With
With wks.Cells(1, 26)
.Value = "Heading 5"
.Font.Bold = True
End With

Many thanks to all

 

[Dave Peterson]

I think that this'll work for you:

Option Explicit

Sub all_sheets()
Dim wks As Worksheet
For Each wks In Worksheets
ChopAndChange wks
Next
End Sub

Sub ChopAndChange(wks As Worksheet)
wks.Range("C:C,G:G,I:I,K:M,P:R").EntireColumn.Hidden = True
wks.Columns(1).Insert
With wks.Cells(1, 1)
.Value = "Heading 1"
.Font.Bold = True
End With
With wks.Range("W1").Resize(1, 4)
.Value = Array( _
"Heading 2", "Heading 3", _
"Heading 4", "Heading 5")
.Font.Bold = True
End With
End Sub

I changed this line:
With Range("W1").Resize(1, 4)
to
With wks.Range("W1").Resize(1, 4)

 

[Tom Ogilvy]

wks.Columns(1).Insert
With wks.Cells(1,1)
.Value = "Heading 1"
.font.Bold = True
End With
with wks.Range("W1").Resize(1,4)
.Value = Array( _
"Heading 2", "Heading 3", _
"Heading 4", "Heading 5")
.font.Bold = True
End With

wks.Cells.Interior.NumberFormat = "@"