Store Value in an Array

[clares]

Hi All

I have been working on an array without success so I am having to start from scrath again.

Would anybody be able to assist me! I have a workbook which has several worksheets in it. What I want to do is go to a certain worksheet, with a workbook, say the "Data" worksheet, and store the name of the following worksheet in a variable, go to the next tab and store that name, go to the next one tab and store that one. I may end up with four or five max worksheet names. Then I would like to go to another workbook altogether, and goto the worksheet that I first stored in the array from the previous workbook.....do what I need to do and then go to the second name stored in the array...do what I want to do so on until I have done every name that was stored.

Any help wouold be greatfully received.

Kindest Regards

Peter

 

[Juan Pablo González]

Use a collection instead

Dim Col As Collection
Dim i As Long

'start it
Set Col = New Collection

'Add the first item
With Workbooks("First.xls")
    Col.Add .Worksheets(.Worksheets("Data").Index + 1).Name
End With

'Add the second one
'Add the first item
With Workbooks("Second.xls")
    Col.Add .Worksheets(.Worksheets("Data").Index + 1).Name
End With

'etc.

Now, loop through the collection
For i = 1 To Col.Count
    MsgBox Col(i)
Next i

 

[clares]

Thanks Juan for your response. Sorry I havent gotten back to you sooner but I haven't had chance to look at your code until now. Amazing I know but unfortunatly the truth.

I'm not sure what this bit is doing - could you confirm= Col.Add .Worksheets(.Worksheets("Data").Index + 1).name) I think that the Index must be the name on the tab and the +1 takes the code to the next tab? Is there anything there that would enable it to forward to the next tab and store its name?

When I step through the code it doesn't pick up the worksheet names.. There are two worksheets after the "Data" worksheets, in myn example Test and Ttest1.

Kind Regards

Peter

 

[Juan Pablo González]

What does it "pick up" then ?