"Sheets.Select" won't work if some sheets are hidden...

[bodo62]

Hi.

I've got a workbook with several worksheets and charts. The charts are based on information from hidden worksheets. I need to select all remaining visible sheets (they are worksheets and charts), and from the group of sheets make a setup for page header and footer as well as printing the whole set to specific printer.

I thought Sheets.Select would select all remaining sheets, but it doesn't work because some sheets are hidden.

I tried also Charts.Select and of course this one will work, but there still are 2 visible worksheets that I want to select together with the charts. How could I solve this problem?

Tanks in advance for any bit of help.

 

[Loomah]

Hi
This ain't pretty but it should work

Option Explicit
Option Base 1
Sub a()
Dim SheetArray() As String
Dim Sh
Dim SheetCount As Integer
For Each Sh In ThisWorkbook.Sheets
    If Sh.Visible = True Then
        SheetCount = SheetCount + 1
        ReDim Preserve SheetArray(SheetCount)
        SheetArray(SheetCount) = Sh.Name
    End If
Next
Sheets(SheetArray()).Select
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bodo62]

Thanks Loomah...

...but I'm sorry to say it's not working. I get a code 9 error on "Sheets(SheetArray()).Select"

 

[Loomah]

Most probable reason is that you've missed the Option Base 1 line at the top of the code.

What this does is set the 'index' of the first element in arrays to 1 rather than the default 0. For some reason that I can't explain this code won't work if the first element of the array is 0 (zero).

anyway, if you don't want to use Option Base 1 then you should initialise the variable SheetCount as -1 so you have

Dim SheetCount As Integer
[b]SheetCount = -1[/b]
For Each Sh In ActiveWorkbook.Sheets

Hope that sorts your problem!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

For some reason that I can't explain this code won't work if the first element of the array is 0 (zero)

You can ignore my comment above - the reason it wouldn't work that way is because of the way I'd written the code!

With a slight change around the code will work without using Option Base 1 or initialising SheetCount to -1

If Sh.Visible = True Then
    ReDim Preserve SheetArray(SheetCount)
    SheetArray(SheetCount) = Sh.Name
    SheetCount = SheetCount + 1
End If

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bodo62]

No, I did have Option Base 1 in my code too, but it did not work. Using SheetCount = -1 just did the job...

Thanks