title: Excel Macro loading Arrays
body: <p>I am not sure how to phrase the question, but I have come up against an issue where I need to load a list of names from a group of sheets into seperate arrays.</p>
  
  <p>For example, <code>Sheet 1</code>, <code>Column A</code> has 10 names to be loaded into <code>array1</code>, <code>Sheet 2</code>, <code>Column A</code> has 14 names to be loaded into <code>array2</code>.</p>
  
  <p>I know this could be hard coded into a macro to load them all but the list of names is not a fixed length and shy of a lot of code I was wondering if this could be shortened.</p>
  
  <p>The way that my mind would tell me to do this is below. </p>
  
  <pre><code>Dim tarray1 As Variant
  Dim tarray2 As Variant
  Dim tarray3 As Variant
  
  For f = 1 To 3
  
      Sheets("Region " &amp; f).Select
      With ActiveSheet
          lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
          tarray&amp; f = ActiveSheet.Range("A2:A" &amp; lastrow)
      End With
  
  
  Next
  </code></pre>
  
  <p>The actual sheet in question has 10 seperate sheets to have the names read in going from Region 1 to 10. So there will be 10 seperate tarrays. This is done for reporting purposes and will be passed back and forth around the sheets.</p>
  
  <p>However <code>tarray &amp; f</code> is obviously not valid and will throw up compile errors. </p>
  
  <p>Can anyone think of a way to get around this?</p>
  
  <p>Hope someone can help.</p>