Store Value in an Array

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Use a collection instead

Code:
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

Well-known Member
Joined
Mar 14, 2002
Messages
557
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,715
Messages
5,597,726
Members
414,169
Latest member
Preston_Cleric

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top