title: Repeating a VBA / Excel macro across multiple work books
body: <p>I have 20 excel workbooks - each identical except that they work with different data. I often need to refine and update the formulae, which I then copy from a source workbook to each of the other workbooks in turn.</p>
  
  <p>I use VBA to record a macro (which I know is not that elegant) to copy and paste the changes required. This is fine, but it's tedious to have to copy the macro 20 times, changing the target workbook name each time.  My preferred solution is to automate it by setting up a loop and calling the macro for each target in turn.</p>
  
  <p>I am new to macros, and am struggling with the right syntax for this.</p>
  
  <p>I've tried the following, but it does not work.  I get a "Object Variable not set" error message, which i don't really understand and can't resolve.</p>
  
  <pre><code>Sub New()
  '
  Dim i As Integer
  Dim target As Workbook
  target(1) = "Workbook1.xlsx"
  target(2) = "Workbook2.xlsx"
  'etc for the other countries
  For i = 1 To 20
  Update
  Next i
  End Sub
  
  Sub Update()
  ' Update macro copies updated cells from workbook Country A.xlsx to target workbook
  Windows("Country A.xlsx").Activate
  Sheets("Tax").Select
  Rows("17:26").Select
  Selection.Copy
  Windows(target(i)).Activate
  Sheets("Tax").Select
  Range("A17").Select
  ActiveSheet.Paste
  ' Etc for other changes required
  End Sub
  </code></pre>
  
  <p>Any help on what i'm missing would be much appreciated.</p>