Lousy with arrays 2

[RonRepp]

I have a sub which will later be turned into a function. I've listed it below is fairly self explanatory.

Sub CopyAllSheets()

    Dim S() As String
    Dim SH
    For i = 1 To Excel.ActiveWorkbook.Sheets.Count
        Sheets(i).Activate

'''Getting a subscript out of range error
        [b][COLOR=blue]S(i) = Sheets(i).Name[/color][/b]
    Next
     
    Sheets(Array(S)).Copy
End Sub

OK, now that you all know I'm an idiot, can someone steer me as to which I'm getting the error message on? I know that there has to be at least one sheet in a workbook, and it errors on i=1. That leads me to believe that it's my array, which I stink at.

Any help will be greatly appreciated.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[PHV]

Dim S() As String
ReDim S(Excel.ActiveWorkbook.Sheets.Count)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RonRepp]

PHV:

Thanks.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[RonRepp]

One more problem.

I'm still getting a subscript out of range on the last line in the code.

[b]Sheets(Array(S())).Copy[/b]

Any ideas?

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[PHV]

Why not simply this ?
Sheets(S).Copy

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GlennUK]

Why not
Sheets.Copy

No need for loops or variables.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Andrzejek]

and it errors on i = 1

You should have somewhere:

[blue]Dim i As integer[/blue]

Have fun.

---- Andy