Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Lousy with arrays 2

Status
Not open for further replies.

RonRepp

Technical User
Feb 25, 2005
1,031
US
I have a sub which will later be turned into a function. I've listed it below is fairly self explanatory.

Code:
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.
 
Dim S() As String
ReDim S(Excel.ActiveWorkbook.Sheets.Count)

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

Thanks.

Ron Repp

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

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

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

Any ideas?

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.
 
Why not simply this ?
Sheets(S).Copy

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top