title: Excel VBA - Copying and Pasting from one Workbook to another with a Dynamic Paste Location
body: <p>I have 50 excel workbooks with the same range "SensResultsCopy" in the same sheet in each of those workbooks ("VAL").  I want to copy the data from those sheets into my open workbook.  The workbook to be copied from is dynamic and taken from the range ("ProjModel"), but I don't know how to make the paste to location dynamic.  The data from each workbook needs to go into its own sheet which has a named range for where the data needs to go, so workbook 1's data needs to go into the sheet named "1" and the range named "SensResPaste1", workbook 2's data needs to go into the sheet named "2" and the range named "SensResPaste1", etc.  I want to make this part of the VBA dynamic using the range "ActiveProjModel" to determine the sheet and the range "PasteRange" as the range to be pasted into:</p>
  
  <p>y.Sheets("1").Range("SensResPaste1")</p>
  
  <p>At the moment I only know how to do this for a specific sheet and range i.e. in the example above, sheet 1.  The full code is below.  Thanks!</p>
  
  <pre><code>Sub DataCapture2()
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.DisplayAlerts = False
  
  Dim x As Workbook
  Dim y As Workbook
  Dim ws1 As Worksheet
  
  '## Open both workbooks first:
  Set y = ThisWorkbook
  Set x = Workbooks.Open(Range("ProjModel"))
  
  'Copy from x:
  x.Sheets("VAL").Range("SensResultsCopy").Copy
  
  'Paste to Worksheet in y:
  y.Sheets("1").Range("SensResPaste1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  'Close x:
  x.Close
  </code></pre>