title: looping through multiple arrays to transfer information between excel workbooks
body: <p>I have written some code that populates a preformatted worksheet in an another workbook, from another preformatted worksheet. They include merged cells and all other things nasty, and for whatever reason cannot be changed.</p>
  
  <p>So, I have written the following</p>
  
  <pre><code>Sub test()
  
  Dim wbkCurrent As Workbook
  'Dim wbk3Mth As Workbook
  Dim wbk6Mth As Workbook
  
  Set wbkCurrent = ThisWorkbook
  Set wbk6Mth = Workbooks.Open("C:\newbook.xlsm")
  
  newbook.Sheets("Mon 1").Activate
  Call assignArrays
  
  End Sub
  
  Sub assignArrays
  
  Call moveValues(32, 3, 7, 8)
  Call moveValues(32, 5, 23, 6)
  Call moveValues(32, 65, 15, 8)
  Call moveValues(32, 56, 31, 5)
  Call moveValues(32, 57, 31, 11)
  Call moveValues(32, 15, 39, 4)
  Call moveValues(32, 16, 39, 5)
  Call moveValues(32, 17, 39, 6)
  Call moveValues(32, 18, 39, 7)
  Call moveValues(32, 30, 39, 10)
  Call moveValues(32, 31, 39, 11)
  Call moveValues(32, 32, 39, 12)
  Call moveValues(32, 33, 39, 13)
  
  Call moveValues(32, 7, 7, 21)
  Call moveValues(32, 9, 23, 19)
  Call moveValues(32, 66, 15, 21)
  Call moveValues(32, 59, 31, 18)
  Call moveValues(32, 60, 31, 24)
  Call moveValues(32, 20, 39, 17)
  Call moveValues(32, 21, 39, 18)
  Call moveValues(32, 22, 39, 19)
  Call moveValues(32, 23, 39, 20)
  Call moveValues(32, 35, 39, 23)
  Call moveValues(32, 36, 39, 24)
  Call moveValues(32, 37, 39, 25)
  Call moveValues(32, 38, 39, 26)
  
  Call moveValues(32, 11, 7, 34)
  Call moveValues(32, 13, 23, 32)
  Call moveValues(32, 67, 15, 34)
  Call moveValues(32, 62, 31, 31)
  Call moveValues(32, 63, 31, 37)
  Call moveValues(32, 25, 39, 30)
  Call moveValues(32, 26, 39, 31)
  Call moveValues(32, 27, 39, 32)
  Call moveValues(32, 28, 39, 33)
  Call moveValues(32, 40, 39, 36)
  Call moveValues(32, 41, 39, 37)
  Call moveValues(32, 42, 39, 38)
  Call moveValues(32, 43, 39, 39)
  
  End Sub
  
  Sub moveValues(tRow, tCol, rRow, rCol)
  'trow is row in this workbook, tcol is column in this workbook, rRow &amp; rCol are the same for the other workbook
  
  ActiveSheet.Cells(rRow, rCol).Value = ThisWorkbook.Sheets("Results").Cells(tRow, tCol).Value
  tRow = tRow + 1
  rRow = rRow + 1
  ActiveSheet.Cells(rRow, rCol).Value = ThisWorkbook.Sheets("Results").Cells(tRow, tCol).Value
  tRow = tRow + 1
  rRow = rRow + 1
  ActiveSheet.Cells(rRow, rCol).Value = ThisWorkbook.Sheets("Results").Cells(tRow, tCol).Value
  tRow = tRow + 1
  rRow = rRow + 1
  ActiveSheet.Cells(rRow, rCol).Value = ThisWorkbook.Sheets("Results").Cells(tRow, tCol).Value
  tRow = tRow + 1
  rRow = rRow + 1
  ActiveSheet.Cells(rRow, rCol).Value = ThisWorkbook.Sheets("Results").Cells(tRow, tCol).Value
  tRow = tRow + 1
  rRow = rRow + 1
  ActiveSheet.Cells(rRow, rCol).Value = ThisWorkbook.Sheets("Results").Cells(tRow, tCol).Value
  
  End Sub
  </code></pre>
  
  <p>This works fine, and writes all the data out. Problem is, I need this to run starting where 
      trow = 2,12,22,32,42,52
  Now I could write this all out manually, but it would mean that going in and changing it later would be a nightmare. So, I had the idea of using <code>a = 2,12,22,32 etc</code> and then having 
      call moveValues(a, 3, 7, 8)
  However this means <code>a</code> bumps up a digit through the moveValues subroutine, and needs resetting each time.</p>
  
  <p>I have one idea to solve this using arrays, but that has its own issues.</p>
  
  <p>I replaced the module <code>assignArrays</code> with</p>
  
  <pre><code>Sub assignArrays()
  
  'row in this workbook
  Dim array1(5)
  array1(5) = Array(2, 12, 22, 32, 42, 52)
  
  'E
  Dim array2(12)
  array2(12) = Array(3, 5, 65, 56, 57, 15, 16, 17, 18, 30, 31, 32, 33)
  
  'U
  Dim array2_1(12)
  array2_1(12) = Array(7, 9, 66, 59, 60, 20, 21, 22, 23, 35, 36, 37, 38)
  
  'R
  Dim array2_2(12)
  array2_2(12) = Array(11, 13, 67, 62, 63, 25, 26, 27, 28, 40, 41, 42, 43)
  
  'row in report
  Dim array3(12)
  array3(12) = Array(7, 23, 15, 31, 31, 39, 39, 39, 39, 39, 39, 39, 39) 'constant in each array 1
  
  'column in report
  Dim array4(12)
  array4(12) = Array(8, 6, 8, 5, 11, 4, 5, 6, 7, 10, 11, 12, 13) '+13 for each third
  
  Dim v1, v2, v3, v4 As Integer
  
  For a = 0 To 5
  v1 = array1(a)
      For b = 0 To 12
          v3 = array3(b)
              For c = 0 To 12
                  v4 = array4(c)
                      For d = 0 To 12
                          v2 = array2(d)
                          Call moveValues(v1, v2, v3, v4)
                      Next d
              Next c
              For c = 0 To 12
                  v4 = array4(c) + 13
                      For d = 0 To 12
                          v2 = array2(d)
                          Call moveValues(v1, v2, v3, v4)
                      Next d
              Next c
              For c = 0 To 12
                  v4 = array4(c) + 26
                      For d = 0 To 12
                          v2 = array2(d)
                          Call moveValues(v1, v2, v3, v4)
                      Next d
              Next c
      Next b
  Next a
  End Sub
  </code></pre>
  
  <p>This dies with a 1004 error on the first line of moveValues. Any ideas to fix either solution?</p>