RE Any ideas why this simple proc. won't work?
 

Bob,

Thanks so much. Works great! One follow up if I may.....Rather than
using the Worksheet names "Sheet1" or "Sheet2" (or whatever they are
named), can I use the VBA name of the sheets as defined is the VBA
Properties window? My users are notorious for changing the sheet
names! Thanks!

Steph,

Try

Public Shtarray
Public sht As Worksheet

Sub ShowAnnually2003()
Shtarray = Array("Sheet1", "Sheet2")
For Each sht In Worksheets(Shtarray)
sht.Columns("A:D").EntireColumn.Hidden = True
Next sht

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steph" wrote in message
m...
I get a Compile Error: Invalid Outside Procedure error, and the
debugger highlights the sheet name in the Shtarray "Dom P&L Ship".
I'm obviously screwing up syntax somewhere! Thanks!


Public Shtarray As Sheets
Public sht As Worksheet

Shtarray = Array("Dom P&L Ship", "Cons P&L Ship")

Sub ShowAnnually2003()

For Each sht In Shtarray
Workbook.Sheet(sht).Columns("A:D").EntireColumn.Hi dden = True
Next

End Sub

RE Any ideas why this simple proc. won't work?
 

Steph,

Here is one way,

Public Shtarray

Sub ShowAnnually2003()
Dim i As Long
Dim dicSheets As Object
Set dicSheets = CreateObject("Scripting.Dictionary")
dicSheets.Add "A1", "A1"
dicSheets.Add "A2", "A2"

For Each sht In ActiveWorkbook.Worksheets
If dicSheets.exists(sht.CodeName) Then
sht.Columns("A:D").EntireColumn.Hidden = True
End If
Next sht

End Sub

The items added to the dictionary are the sheet codenames.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steph" wrote in message
om...
Bob,

Thanks so much. Works great! One follow up if I may.....Rather than
using the Worksheet names "Sheet1" or "Sheet2" (or whatever they are
named), can I use the VBA name of the sheets as defined is the VBA
Properties window? My users are notorious for changing the sheet
names! Thanks!

Steph,

Try

Public Shtarray
Public sht As Worksheet

Sub ShowAnnually2003()
Shtarray = Array("Sheet1", "Sheet2")
For Each sht In Worksheets(Shtarray)
sht.Columns("A:D").EntireColumn.Hidden = True
Next sht

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steph" wrote in message
m...
I get a Compile Error: Invalid Outside Procedure error, and the
debugger highlights the sheet name in the Shtarray "Dom P&L Ship".
I'm obviously screwing up syntax somewhere! Thanks!


Public Shtarray As Sheets
Public sht As Worksheet

Shtarray = Array("Dom P&L Ship", "Cons P&L Ship")

Sub ShowAnnually2003()

For Each sht In Shtarray
Workbook.Sheet(sht).Columns("A:D").EntireColumn.Hi dden = True
Next

End Sub

RE Any ideas why this simple proc. won't work?
 

Yes. Check out the CodeName property.

--
George Nicholson

Remove 'Junk' from return address.


"Steph" wrote in message
om...
Bob,

Thanks so much. Works great! One follow up if I may.....Rather than
using the Worksheet names "Sheet1" or "Sheet2" (or whatever they are
named), can I use the VBA name of the sheets as defined is the VBA
Properties window? My users are notorious for changing the sheet
names! Thanks!

Steph,

Try

Public Shtarray
Public sht As Worksheet

Sub ShowAnnually2003()
Shtarray = Array("Sheet1", "Sheet2")
For Each sht In Worksheets(Shtarray)
sht.Columns("A:D").EntireColumn.Hidden = True
Next sht

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steph" wrote in message
m...
I get a Compile Error: Invalid Outside Procedure error, and the
debugger highlights the sheet name in the Shtarray "Dom P&L Ship".
I'm obviously screwing up syntax somewhere! Thanks!


Public Shtarray As Sheets
Public sht As Worksheet

Shtarray = Array("Dom P&L Ship", "Cons P&L Ship")

Sub ShowAnnually2003()

For Each sht In Shtarray
Workbook.Sheet(sht).Columns("A:D").EntireColumn.Hi dden = True
Next

End Sub