title: Runtime Error 1004 Method "Range" of object "_Worksheet" failed
body: <p>I am trying to combine numerous sheets into one new sheet. I would really appreciate any comments.</p>
  
  <p>The issue is with the line:</p>
  
  <pre><code>wsSrc.Range("A1", wsSrc.Range("D", lastRow)).Copy Destination:=rngDest 
  </code></pre>
  
  <p>Which causes the error when I try to run it. I have previously been using the code to combine all the sheets into the sheet Summary which is where the button for the macro is created which worked fine.</p>
  
  <pre><code>Sub mcrCombine()
  
  ActiveWorkbook.Sheets.Add.Name = "Combined" 'Create new sheet
  
  'Definitions
  Dim wsSrc As Worksheet
  Dim wsDest As Worksheet
  Dim rngDest As Range
  Dim lastRow As Long
  Dim destRow As Long
  Set wsDest = Worksheets("Combined") 'Destination sheet in same Workbook
  Set rngDest = wsDest.Range("B1") 'Destination cell in Combined
  Application.DisplayAlerts = False 'suppress prompt worksheet delete
  
  'loop through all sheets
  For Each wsSrc In ThisWorkbook.Sheets
      If wsSrc.Name &lt;&gt; "Summary" And wsSrc.Name &lt;&gt; "Combined" Then 'all sheets except summary
          lastRow = wsSrc.Cells.SpecialCells(xlCellTypeLastCell).Row 'define     last row
          wsSrc.Range("A1", wsSrc.Range("D", lastRow)).Copy Destination:=rngDest 'copy and paste data in range
          Set rngDest = rngDest.Offset(lastRow - 1) 'update destination range
          wsSrc.Delete 'delete source file
      End If
  Next
  Application.DisplayAlerts = True 'prompts back on
  End Sub
  </code></pre>