title: Copy and paste columns between sheets based on an Excel "Map" or table? - Further autoexecute file links?
body: <p>I have been doing this manually for far too long, and I feel there must be a way to speed up this process. Hopefully you guys can help me.</p>
  
  <p>Currently I have an excel file that has written in VBA macros that copy and paste certain columns from one sheet and paste them into another sheet. There are about 160 of these written, each with about 10 copy/paste commands. (This workbook is called <code>workbook A</code>)</p>
  
  <p>Currently my method involves me opening up <code>Workbook B</code>, copying the data into a sheet in <code>Workbook A</code>, selecting a macro to run from a dropdown in <code>Workbook A</code>, copying the results and pasting them into a "master" <code>Workbook C</code>. The problem for me is that There are frequent instances when maps - that is the column locations of data - change in <code>Workbook B</code>. I maintain a "master map" file that looks something like this:</p>
  
  <pre><code>Contract# | Purchaser | Price | Quantity | Total
  ------------------------------------------------
  A         |  B        |  C    |  D       |  E
  ------------------------------------------------
  G         |  D        |  C    |  A       |  B
  ------------------------------------------------
  </code></pre>
  
  <p>etc. (Apologies if that's messy)</p>
  
  <p>What I would like to do is have the sheet do the copying and pasting automatically based on A: The column on the map (the pasting column) and B: the letter represented in the row for that particular Contract (this copies the column of which the letter represented). </p>
  
  <p>Is this possible?</p>
  
  <p>Secondly, if that is - The option to have this execute automatically by specifying the file patch of each file would be outstanding (I have a clear taxonomy of file locations and names). Is that also possible?</p>
  
  <ul>
  <li>Added condensed sample of ran macro.</li>
  </ul>
  
  <p>The macro is quite simple, here is a sample... </p>
  
  <pre><code> Sub PA979()
   Application.ScreenUpdating = False
  
     'Retail $
      Sheets("VSR Input").Select
     Range("x1:x5004").Copy
     Sheets("Sheet1").Select
     Range("q4").Select
     ActiveSheet.Paste
  
     'PA $
      Sheets("VSR Input").Select
     Range("y1:y5004").Copy
     Sheets("Sheet1").Select
     Range("s4").Select
     ActiveSheet.Paste
  
  'Q
      Sheets("VSR Input").Select
     Range("z1:z5004").Copy
     Sheets("Sheet1").Select
     Range("t4").Select
     ActiveSheet.Paste
  
     'Total $
      Sheets("VSR Input").Select
     Range("aa1:aa5004").Copy
     Sheets("Sheet1").Select
     Range("u4").Select
     ActiveSheet.Paste
     Range("A1").Select
  
      Dim ws As Worksheet
  Set ws = Worksheets("Sheet1")
  
  Dim usedRows As Long
  usedRows = ws.Cells(ws.Rows.Count, "U").End(xlUp).Row
  Application.ScreenUpdating = False
  
      Sheets("Sheet1").Select
  Range("v3").Select
     ActiveCell.FormulaR1C1 = "PA#"
     ActiveCell.Offset(1, 0).Range("A1").Select
     ActiveCell = "979"
      ActiveCell.Select
      Selection.AutoFill Destination:=ActiveCell.Range(Cells(1, 1), Cells(usedRows - 3, 1)), Type:= _
          xlFillDefault
          Range("v4").Select
          Selection.Copy
      Range(Selection, Selection.End(xlDown)).Select
      ActiveSheet.Paste
  
      ActiveSheet.Range("A1").Select
  
  End Sub `
  </code></pre>