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

--
Message posted from http://www.ExcelForum.com

Hi
try something like

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


--
Regards
Frank Kabel
Frankfurt, Germany


I have a Sub called ColHidden() which i want to run on every
worksheet
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
the afforementioned workbook?

I am using Excel 2000.

TIA


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Regards,
Tom Ogilvy



"Jako " wrote in message
...
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 the
flexibility of just calling a Subroutine as mentioned to run on every
sheet, but to be able to chop and change to the Subroutine itself.

Any ideas??

Thanks again


---
Message posted from http://www.ExcelForum.com/

Fantastic..

Thanks again Tom

--
Message posted from http://www.ExcelForum.com

OOoooopps!!

Spoke to soon Tom.

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

comes up with Type mismatch error !!

--
Message posted from http://www.ExcelForum.com

Try:

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

--

Vasant




"Jako " wrote in message
...
OOoooopps!!

Spoke to soon Tom.

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

comes up with Type mismatch error !!!


---
Message posted from http://www.ExcelForum.com/

Try it like this:

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

--
Regards,
Tom Ogilvy

"Jako " wrote in message
...
OOoooopps!!

Spoke to soon Tom.

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

comes up with Type mismatch error !!!


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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 = "@"

--
Regards,
Tom Ogilvy

"Jako " wrote in message
...
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 Insert
a column into "A" (so it is the first column on the worksheet) called
"Heading 1" then insert from column W - Z
headings "Headig 2", "Heading 3", "Heading 4" and "Heading 5" all in
bold font.
After this i then want to make all cells on the worksheet as Text
format.

Please advise

Many thanks


---
Message posted from http://www.ExcelForum.com/

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.Hidd en = 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

--
Message posted from http://www.ExcelForum.com

Jako,

Not realy sure, but change:

For Each wks In Worksheets

to

For Each wks In Workbook

HTH

Charle

--
Message posted from http://www.ExcelForum.com

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.Hidd en = 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

--
Message posted from http://www.ExcelForum.com

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.Hidd en = 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)



"Jako <" wrote:

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.Hidd en = 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.

TIA

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

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 = "@"


--
Regards,
Tom Ogilvy


"Jako " wrote in message
...
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.Hidd en = 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.

TIA


---
Message posted from http://www.ExcelForum.com/