title: VBA - copy a dynamic range to a new workbook
body: <p>I'm trying to figure out how to copy a dynamic range into a new workbook. The actual project is to generate monthly budget reports based on the user's choice of month. The overarching system tracks a number of budget lines, where each line has its own sheet, and each sheet holds 12 tables for the fiscal year for expenses to be input; it all feeds back into an annual budget sheet. Upon the user picking a month, a new workbook will be created, mirroring the number of sheets and filling each sheet with that month's table. Each table is a dynamic range.</p>
  
  <p>What I've got below is a dry run to work out the mechanics, but the problem is that I cannot get the dynamic range to paste correctly:</p>
  
  <pre><code>Sub pasting()
  
  On Error Resume Next
  
  Dim x As Workbook
  Dim y As Workbook
  
  'set the budget tracking system as the active workbook
  Set x = Workbooks("Save and copying proof of concept.xlsm")
  
  'activate budget tracking system
  x.Activate
  
  Set y = Workbooks.Add
  
  Dim z As Range
  Dim w As Range
  
  
  'test copying two cells in two sheets into new sheets in the new workbook
  Set z = Workbooks("Save and copying proof of concept.xlsm").Sheets("Sheet1").Range("A1")
  Set w = Workbooks("Save and copying proof of concept.xlsm").Sheets("Sheet2").Range("A1")
  
  'call saveas option for monthly workbook
  With y
      Call save_workbook_newName
  End With
  
  'add 8 sheets to new workbook for 8 budget lines
  Dim v As Worksheet
  Dim i As Integer
  
  For i = 1 To 7
     Sheets.Add
  
  Next i
  
  'copy the specified range from the original sheet and into the newly created workbook.
  
  z.Copy
  y.Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
  
  w.Copy
  y.Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
  
  'copy a dynamic range to a new workbook
  
  x.Worksheets("Sheet3").Activate
  
  Dim xRow As Long, xColumn As Long
  
  'determine the row and column limits of the dynamic range
  Range("A100").End(xlUp).Select
  xRow = ActiveCell.Row
  
  Range("D").End(xlToLeft).Activate
  xColumn = ActiveCell.Column
  
  'select the range specified by the dynamic boundaries
  Range(Cells(1, 1), Cells(xRow, xColumn)).Select
  
  Selection.Copy
  
  'activate newly created workbook
  y.Worksheets("Sheet3").Activate
  
  'paste into the new workbook
  Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, SkipBlanks:=False, _
      Transpose:=False
  
  End Sub
  
  
  
  Sub save_workbook_newName()
  
      Dim workbook_name As Variant
  
      'display the SaveAs dialog box
      'once a name is provided, the GetSaveAsFilename method gets the particular name and _
      'assigns that name to the workbook_name variable
  
      workbook_name = Application.GetSaveAsFilename
  
      'if the user provides a filename, the true condition is executed, and if not (presses Cancel), the false condition is executed.
      If workbook_name &lt;&gt; False Then
  
      'the application.acriveworkbook property returns the workbooks to the current active window
      'saves the file with the file name given by the user.
  
          ActiveWorkbook.SaveAs Filename:=workbook_name &amp; "xlsx"
      Else
          ActiveWorkbook.Close
      End If
  
  End Sub
  </code></pre>
  
  <p>This bit is the problematic code:</p>
  
  <pre><code>Range("A100").End(xlUp).Select
  xRow = ActiveCell.Row
  
  Range("D").End(xlToLeft).Activate
  xColumn = ActiveCell.Column
  
  'select the range specified by the dynamic boundaries
  Range(Cells(1, 1), Cells(xRow, xColumn)).Select
  </code></pre>
  
  <p>It essentially only copies column A, even if it's told to activate column D and choose everything to the left of it (Columns A to C hold random numbers).</p>
  
  <p>Using this method for selecting a dynamic range did not yield good results:</p>
  
  <pre><code>LR = Range("D1000").End(xlUp).Row
  Set R1 = Range("D1:E" &amp; LR)
  </code></pre>
  
  <p>Thanks, and I appreciate your help in this respect!</p>