title: Procedure too large error from transferring large number of named ranges
body: <p>I need to import a large set of data from one Excel workbook to another. I cannot use queries or any other data connection. The thing is, columns (number and order) change over time.</p>
  
  <p>So I defined names for 206 columns of the source workbook in VBA (<code>Dim xyz_Source As Long</code>). Then I search for the locations of those 206 columns (<code>xyz_Source = Application.WorksheetFunction.Match("xyz", Source.Range, 0)</code> and create a range (<code>Source.Range(Cells(2, xyz_Source), Cells(LastRow, xyz_Source</code>).</p>
  
  <p>Afterwards I do the same for the target file (<code>Dim xyz_Target As Long &amp; xyz_Target = Application.WorksheetFunction.Match("xyz", Target.Range, 0)</code>) and put it togehter as a <code>range</code>.</p>
  
  <p>Eventually, I copy them individually and paste them into the target file (also individually), one at a time.</p>
  
  <p>That basically creates an entire book of code for this simple procedure. And Excel throughs a "procedure too large" back at me.</p>
  
  <p>Do you know any smart way of shortening the code / looping through / outsourcing parts to other modules; i.e. make it more intelligent?</p>
  
  <p>Any advice is really appreciated.
  Thanks a lot in advance!</p>
  
  <p>Here's an example/extract of my code:</p>
  
  <pre><code>Dim Column_Name_1_Source As Long
  Dim Column_Name_2_Source As Long
  Dim Column_Name_3_Source As Long
  Dim Column_Name_4_Source As Long
  Dim Column_Name_5_Source As Long
  Dim Column_Name_6_Source As Long
  Dim Column_Name_7_Source As Long
  Dim Column_Name_8_Source As Long
  Dim Column_Name_9_Source As Long
  Dim Column_Name_10_Source As Long
  
  Column_Name_1_Source = Application.WorksheetFunction.Match("Column Name 1", Source.Range("10:10"), 0)
  Column_Name_2_Source = Application.WorksheetFunction.Match("Column Name 2", Source.Range("10:10"), 0)
  Column_Name_3_Source = Application.WorksheetFunction.Match("Column Name 3", Source.Range("10:10"), 0)
  Column_Name_4_Source = Application.WorksheetFunction.Match("Column Name 4", Source.Range("10:10"), 0)
  Column_Name_5_Source = Application.WorksheetFunction.Match("Column Name 5", Source.Range("10:10"), 0)
  Column_Name_6_Source = Application.WorksheetFunction.Match("Column Name 6", Source.Range("10:10"), 0)
  Column_Name_7_Source = Application.WorksheetFunction.Match("Column Name 7", Source.Range("10:10"), 0)
  Column_Name_8_Source = Application.WorksheetFunction.Match("Column Name 8", Source.Range("10:10"), 0)
  Column_Name_9_Source = Application.WorksheetFunction.Match("Column Name 9", Source.Range("10:10"), 0)
  Column_Name_10_Source = Application.WorksheetFunction.Match("Column Name 10", Source.Range("10:10"), 0)
  
  Dim Column_Name_1_Target As Long
  Dim Column_Name_2_Target As Long
  Dim Column_Name_3_Target As Long
  Dim Column_Name_4_Target As Long
  Dim Column_Name_5_Target As Long
  Dim Column_Name_6_Target As Long
  Dim Column_Name_7_Target As Long
  Dim Column_Name_8_Target As Long
  Dim Column_Name_9_Target As Long
  Dim Column_Name_10_Target As Long
  
  Column_Name_1_Target = Application.WorksheetFunction.Match("Column Name 1", Target.Range("9:9"), 0)
  Column_Name_2_Target = Application.WorksheetFunction.Match("Column Name 2", Target.Range("9:9"), 0)
  Column_Name_3_Target = Application.WorksheetFunction.Match("Column Name 3", Target.Range("9:9"), 0)
  Column_Name_4_Target = Application.WorksheetFunction.Match("Column Name 4", Target.Range("9:9"), 0)
  Column_Name_5_Target = Application.WorksheetFunction.Match("Column Name 5", Target.Range("9:9"), 0)
  Column_Name_6_Target = Application.WorksheetFunction.Match("Column Name 6", Target.Range("9:9"), 0)
  Column_Name_7_Target = Application.WorksheetFunction.Match("Column Name 7", Target.Range("9:9"), 0)
  Column_Name_8_Target = Application.WorksheetFunction.Match("Column Name 8", Target.Range("9:9"), 0)
  Column_Name_9_Target = Application.WorksheetFunction.Match("Column Name 9", Target.Range("9:9"), 0)
  Column_Name_10_Target = Application.WorksheetFunction.Match("Column Name 10", Target.Range("9:9"), 0)
  
  ‘Column_Name_1:
  Source.Range(Cells(11, Column_Name_1_Source), Cells(Lastrow_Source, Column_Name_1_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_1_Target), Cells(Lastrow_Ziel, Column_Name_1_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_2:
  Source.Range(Cells(11, Column_Name_2_Source), Cells(Lastrow_Source, Column_Name_2_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_2_Target), Cells(Lastrow_Ziel, Column_Name_2_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_3:
  Source.Range(Cells(11, Column_Name_3_Source), Cells(Lastrow_Source, Column_Name_3_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_3_Target), Cells(Lastrow_Ziel, Column_Name_3_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_4:
  Source.Range(Cells(11, Column_Name_4_Source), Cells(Lastrow_Source, Column_Name_4_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_4_Target), Cells(Lastrow_Ziel, Column_Name_4_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_5:
  Source.Range(Cells(11, Column_Name_5_Source), Cells(Lastrow_Source, Column_Name_5_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_5_Target), Cells(Lastrow_Ziel, Column_Name_5_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_6:
  Source.Range(Cells(11, Column_Name_6_Source), Cells(Lastrow_Source, Column_Name_6_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_6_Target), Cells(Lastrow_Ziel, Column_Name_6_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_7:
  Source.Range(Cells(11, Column_Name_7_Source), Cells(Lastrow_Source, Column_Name_7_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_7_Target), Cells(Lastrow_Ziel, Column_Name_7_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_8:
  Source.Range(Cells(11, Column_Name_8_Source), Cells(Lastrow_Source, Column_Name_8_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_8_Target), Cells(Lastrow_Ziel, Column_Name_8_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_9:
  Source.Range(Cells(11, Column_Name_9_Source), Cells(Lastrow_Source, Column_Name_9_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_9_Target), Cells(Lastrow_Ziel, Column_Name_9_Target)).PasteSpecial xlPasteValues
  ‘Column_Name_10:
  Source.Range(Cells(11, Column_Name_10_Source), Cells(Lastrow_Source, Column_Name_10_Source)).SpecialCells(xlCellTypeVisible).Copy
  Target.Range(Cells(10, Column_Name_10_Target), Cells(Lastrow_Ziel, Column_Name_10_Target)).PasteSpecial xlPasteValues
  </code></pre>
  
  <p>New code with loop (still with errors):</p>
  
  <pre><code>Dim colname_Target As Variant
  Dim colnum_Target As Variant
  Dim colnum_Source As Variant
  Dim i_Target As Long
  Dim Unique_ID_Target As Long
  
  Unique_ID_Target = Application.WorksheetFunction.Match("Unique Identifier", Target.Range("9:9"), 0)
  colname_Target = Application.Transpose(Application.Transpose(Target.Range(Cells(9, 1).Address, Cells(9, Unique_ID_Target - 1).Address).Value2))
  
  ReDim colnum_Target(Unique_ID_Target)
  ReDim colnum_Source(Unique_ID_Target)
  
  For i_Target = LBound(colname_Target) To UBound(colname_Target) Step 1
      colnum_Target(i_Target) = Target.Rows(9).Find(What:=colname_Target(i_Target), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
  Next i_Target
  
  For i_Target = LBound(colname_Target) To UBound(colname_Target) Step 1
      colnum_Source(i_Target) = Source.Rows(10).Find(What:=colname_Target(i_Target), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
  Next i_Target
  </code></pre>