selecting range over multiple sheets

T

Tanya

Hi
I am trying to select a columns that are hidden in sheets 1-10 and then
unhide them.
Below is as far as I have gotten. Selecting the sheets and columns appears
to work fine, the problem appears to be in activating said cells in order to
unhide them in each sheet. The following code without the line
'Worksheets(Array.....)).Activate only unhides he columns on sheet1.

Sub Show()

'Show hidden outcome columns
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10")).Select
'Worksheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9",
"10")).Activate<<<problem

Columns("D:O").Select
Selection.EntireColumn.Hidden = False
Range("D5").Select


End Sub

If anyone can help I would appreciate it.

Cheers
Tanya
 
H

HI

How about this one.
Sub test()
Dim Sh As Worksheet
Set area = Sheets(Array(2, 3, 4)) /*You can use your selection
here*/
For Each Sh In area
Sh.Select
Range("P:p,O:O,E:E").Select /*Also use your selection here too*/
Selection.EntireColumn.Hidden = False
Next Sh
End Sub
 
T

Tanya

Thank you HI

Your procedure worked and I amended it as follows so that after the columns
are visible I return to sheet 'Setup'

Sub Show()
Dim ws As Worksheet
Set area = Sheets(Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11))
For Each ws In area
ws.Select
Columns("D:O").Select
Selection.EntireColumn.Hidden = False
Next ws
Sheets("Setup").Activate
End Sub

Kind Regards
Tanya
 
B

Bill Renaud

Tanya: You shouldn't need to use a For loop to iterate through all of the
worksheets unhiding the columns. Select all of the sheets and unhide the
columns at once, just like you would if you record the code using the macro
recorder. Try the following code:

Worksheets(Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11)).Select

With Worksheets(2)
.Activate
.Columns("D:O").Hidden = False
.Range("A1").Select

'Ungroup the worksheets.
.Select
End With

Sheets("Setup").Activate
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top