Syntax in Varying Object Names

[Guest]

Hello:

I have a workbook which reads and summarizes data from anywhere from 2 to 7
workbooks.

I want to set up a single For-Next Loop and some other statements to prevent
me from hardcoding six different cases. I need to know what is the proper
syntax for referring to worksheet objects whose names can vary.

Example:
Assume that the target workbook (which contains the code) is named
TARGET.xls. The worksheet object where the data will be summarized is called
DATASUM (a worksheet within TARGET). The user chooses the number of books (2
to 7) to read data from, and this variable is called NUMBEROFBOOKS.

Assume that the data source books are named GROUP1.xls, GROUP2.xls, ...
GROUP7.xls. There is a worksheet in each of the workbooks called DATA.
I have chosen to name those worksheet objects as follows: GRP1DATA,
GRP2DATA ... GRP7DATA.

Here is an example of what I need to know:

For N = 2 to NUMBEROFBOOKS
....
DATASUM.Cells(1,1) = DATASUM.Cells(1,1) + GRP{&N&}DATA.Cells(1,1)

' What is the proper syntax for varying the object name within a loop like
this? - brackets {} added to make it easier to spot.
....
Next N

I hope this is clear. I know there are other ways of doing this, but this
is a very simplified example of just one thing I need to do. if someone can
advise me of the proper syntax, I think I can work the rest of it out.

Late thought: Maybe I answered my own question. Is something like this
legitimate? (I haven't tried it):

"GRP"&N&"DATA".Cells(1,1)

Thanks,
MARTY

 

[Guest]

Follow up:

"GRP"&N&"DATA".Cells(1,1) is not legit. It yields an expecting end of
statement error.

I also tried moving the N to the end, i.e.: "GRPDATA"&N.Cells(1,1). This
doesn't work either. It's expecting an object called N.

Any other ideas? I'm getting really curious as to whether this can actually
be done.