Show / Hide Columns in multiple sheets

[Shadster]

Hi all.

I am having a slight problem hiding and showing columns across multiple
workbooks. I have created an array that selects multiple sheets and
selects the required columns to hide. When I then try and hide them it
only hides the columns on the visible sheet. I could manually select
each and every sheet in the workbook and run it that way but it seems
very inefficient when they are already selected. Does anyone know of a
way around this problem?

I am using Excel 2000 and this is basically the code I am using:

Code:
--------------------

Sub MonthFormatter()

Dim strCurrMnth

strCurrMnth = Range("A1").Value

Sheets(Array("Consolidated", "Fees Billed", "Other Income", "Bad Debt & Disbs WO", _
"Partner Charges", "Fee Earner Related Costs", "Support Charges", "Travel", _
"Fee Earner Payroll", "Support Associated Costs", "Partner Costs", "Training", _
"Marketing", "Other Costs")).Select
Columns("D:S").Select
Selection.EntireColumn.Hidden = False
Select Case strCurrMnth
Case "Show All"
Columns("D:S").Select
Selection.EntireColumn.Hidden = False
Range("B1").Select
Case "May"
Columns("G:S").Select
Selection.EntireColumn.Hidden = True
Range("B1").Select
Case "June"
Columns("G:S").Select
Selection.EntireColumn.Hidden = True
Range("B1").Select
End Select
Sheets("Consolidated").Select
Range("B1").Select

End Sub

 

[ozcank]

Try something like

Sheets("Consolidated").Activate
Columns("D:S").Select
Selection.EntireRow.Hidden = False

Sheets("Fees Billed").Activate
Columns("G:S").Select
Selection.EntireRow.Hidden = False

Sheets("Other Income").Activate
Columns("G:S").Select
Selection.EntireRow.Hidden = False

and so on...


Oz

 

[Shadster]

Cheers Oz

That will work but seems very inefficient when all the sheets are
already selected. If I manually select hide columns with multiple
sheets selected it hides them on all the sheets.

If I then record that same action and play it back it only does the
active sheet. I have approximately 20 workbooks each with different
sheet names that this code has to be applied to so activating each
sheet and then executing the code will be a very time consuming process
to code and to execute.

Surely their is a way to execute the code on all the sheets highlighted
without having to activate each sheet in turn??

Shad