vba sheet referencing help

[bolo]

Hi i was wondering if anyone can clear something up for me.

I have 8 sheets, named cpt1 to cpt8 in vba.

Now i need to loop through these 8 sheets, storing the same range of each sheet into a matrix. I have the following

dim test () as variant

For i = 1 to 8
(1) test = sheets(i).Range("b2:u21")
(2) test = cpt1.Range("b2:u21")
next i

Now (1) doesn't work, but (2) does. However i do not know how to reference the cpt1 sheet name using the for loop (ie "cpt" & i) Can anyone help me with getting this to work?

Thanks

Bolo

 

[VoG]

Try

test = Sheets("cpt" & i).Range("b2:u21")

 

[bolo]

Try

test = Sheets("cpt" & i).Range("b2:u21")

Doesn't work. to me it seems "sheets" and Range don't seem to work with each other.

 

[Norie]

Are these the only sheets?

For Each ws In Worksheets
     test = ws.Range("b2:u21") 
Next ws

If they aren't we can easily add some condition(s) to the code.

For Each ws In Worksheets
     If Left(ws.Name,3)="cpt" Then test = ws.Range("b2:u21") 
Next ws

 

[bolo]

Are these the only sheets?

For Each ws In Worksheets
     test = ws.Range("b2:u21") 
Next ws

If they aren't we can easily add some condition(s) to the code.

For Each ws In Worksheets
     If Left(ws.Name,3)="cpt" Then test = ws.Range("b2:u21") 
Next ws

Hi Norrie,

I need to use the i notation really, as the i will be used in calculations later.

 

[Norie]

Well what VogII posted should work.

And Sheets and Range should work together no problem.

 

[bolo]

Well i know what yogII suggested doesn't work!!!

If you look at the sheets collection property in the help, Range is not mentioned in help. Hence why i thought they don't work together. Plus it is the only thing that is causing the error.

 

[Norie]

What error?

It's always a good idea to tell us what the error is.

 

[erik.van.geit]

Hi,

not sure if it can be done easier
first I would suggest

Dim test As Variant

without brackets !

this works for me

Sub testit()
Dim test As Variant
Dim i As Integer

For i = 1 To 8
test = Sheets(ActiveWorkbook.VBProject.VBComponents("cpt" & i).Name).Range("b2:u21")
Next i

End Sub

regards,
Erik

 

[bolo]

Hi,

not sure if it can be done easier
first I would suggest

Dim test As Variant

without brackets !

this works for me

Sub testit()
Dim test As Variant
Dim i As Integer

For i = 1 To 8
test = Sheets(ActiveWorkbook.VBProject.VBComponents("cpt" & i).Name).Range("b2:u21")
Next i

End Sub

regards,
Erik

Erik, do i need to load a reference to make this work?

I found the reference library that i need. However i get a subscript out of range error (run time error 9) with your exact code Erik. Any ideas?