selecting range over multiple sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tanya - 07 Dec 2007 12:08 GMT

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

Reply to this Message

HI - 07 Dec 2007 12:29 GMT

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

> Hi
> I am trying to select a columns that are hidden in sheets 1-10 and then
[quoted text clipped - 21 lines]
> Cheers
> Tanya

Reply to this Message

Tanya - 08 Dec 2007 05:21 GMT

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

Reply to this Message

Bill Renaud - 10 Dec 2007 18:53 GMT

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

Signature

Regards,
Bill Renaud

Reply to this Message