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

 

[erik.van.geit]

"subscript out of range error" means that your sheetname is not correct

 

[Legacy 98055]

Understand the difference between a worksheet's "Name" and a worksheet's "CodeName"...

Eric's code latebinds to Microsoft Visual Basic for Applications Extensibility. This will error out unless you have allowed access to your vb project in Tools>Macros>Security... I would not lower your security. At least not in this case. Just create a function that will search your worksheet codenames and return the correct reference...

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
       <font color="#0000A0">Dim</font> test(1 <font color="#0000A0">To</font> 8) <font color="#0000A0">As</font> <font color="#0000A0">Variant</font>
       <font color="#0000A0">Dim</font> sh <font color="#0000A0">As</font> Worksheet
       <font color="#0000A0">Dim</font> i <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>

       <font color="#0000A0">For</font> i = 1 <font color="#0000A0">To</font> 8
           <font color="#0000A0">Set</font> sh = GetSheetRefByCodename("cpt" & i)
           <font color="#0000A0">If</font> <font color="#0000A0">Not</font> sh <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
               test(i) = Sheets(i).Range("b2:u21")
           <font color="#0000A0">Else</font>
              <font color="#008000"> 'reference is equal to nothing</font>
              <font color="#008000"> 'this codename does not exist</font>
           <font color="#0000A0">End</font> <font color="#0000A0">If</font>
       <font color="#0000A0">Next</font> i
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Public</font> <font color="#0000A0">Function</font> GetSheetRefByCodename(CodeName <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> Worksheet
       <font color="#0000A0">Dim</font> sh <font color="#0000A0">As</font> Worksheet
       <font color="#0000A0">For</font> <font color="#0000A0">Each</font> sh <font color="#0000A0">In</font> ThisWorkbook.Worksheets
           <font color="#0000A0">If</font> sh.CodeName = CodeName <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">For</font>
       <font color="#0000A0">Next</font>
       <font color="#0000A0">Set</font> GetSheetRefByCodename = sh
  <font color="#0000A0">End</font> <font color="#0000A0">Function</font>
</FONT></td></tr></table><button onclick='document.all("414200762150234").value=document.all("414200762150234").value.replace(/<br \/>\s\s/g,"");document.all("414200762150234").value=document.all("414200762150234").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("414200762150234").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="414200762150234" wrap="virtual">
Sub Example()
Dim test(1 To 8) As Variant
Dim sh As Worksheet
Dim i As Integer

For i = 1 To 8
Set sh = GetSheetRefByCodename("cpt" & i)
If Not sh Is Nothing Then
test(i) = Sheets(i).Range("b2:u21")
Else
'reference is equal to nothing
'this codename does not exist
End If
Next i
End Sub

Public Function GetSheetRefByCodename(CodeName As String) As Worksheet
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.CodeName = CodeName Then Exit For
Next
Set GetSheetRefByCodename = sh
End Function</textarea>

 

[erik.van.geit]

Hi,

sorry, my code was wrong
I assumed that

ActiveWorkbook.VBProject.VBComponents("cpt" & i).Name

was returning the tabname
since tabnames and codenames were the same, I didn't see the error in my thinking.
RightClick: as always, simple and nice workaround