VBA Loop

[GRCArizona]

Hi - I've got a worksheet that summarizes data from several other Client workbooks. I've written VBA code that opens up the first Client workbook and pulls all the info that I'm looking for. What I'm looking for is something that at the end of my current code (for Client 1) will loop back to the beginning of the code and repeat the code for the remaining Client workbooks.

What I have is a range of cells (A11:A15) with 1,2,3,4,5 which represents the number of Client workbooks. I have the following code at the beginning of my macro:

Dim sClient_Selection As String
sClient_Selection = Range("Client1").Value

What I'm looking for is something where I can change the "1" in Range("Client1").Value to "2", then re-run the code then go back to the beginning of the code and change it to "3", etc.

It would be easy for me to copy the code and simply change the "1" to "2", etc, but I have A LOT of code for "1" and the macro code will get too lengthy very quickly.

Isn't there sth like a For each i, or Loop Until?

Any help would be appreciated.
thx,
GRC

 

[AlphaFrog]

Here are a couple of ways you might try...

Dim i as Integer
For i = 1 to 5
sClient_Selection = Range("Client" & i).Value
Next i

Dim Client As Variant
For Each Client In Array("Client1", "Client2", "Client3", "Client4", "Client5")
sClient_Selection = Range(Client).Value
Next Client

 

[GRCArizona]

That's it! Perfect, thx a bunch. I knew it was sth like that. I was putting extra quotes around the "i".

GRC

 

[GRCArizona]

Actually, the code is erroring out when it trys to start bringing over the info for the 3rd client. Any thoughts...?

Dim i As Integer
For i = 1 To 5

Dim sClient_Selection As String
sClient_Selection = Range("Client" & i).Value

Workbooks.Open Filename:= _
"" & sClient_Selection & ""

 

[GRCArizona]

Disregard. I'm an idiot. I've figured it out.