title: Excel Visual Basic to set SourceRange
body: <p>I am getting a run-time error 1004 Application-defined or object-defined error.
  I have some VB in Excel that I am trying to get it to select a range (that possibly may change).. it is erroring out on  </p>
  
  <pre><code> Set SourceRange = WorkBk.Sheets("Sheet8").Range("B14", Range("XFD14").End(XltoLeft).Offset(0,2))
  </code></pre>
  
  <p>Need to be able to have the SourceRange be range B14(always) to the last available cell + 2 to the right (for just in cases) </p>
  
  <p>I have:</p>
  
  <pre><code> Sub MergeData()
   Dim SourceRange as Range
   WorkBk As Workbook
   Dim DestRange as Range
   Dim FolderPath As String
   Dim FileName As String
   Dim NRow As Long
   FolderPath = Sheets("Sheet1").Range("D7")
   NRow = 4
   FileName = DIR(FolderPath &amp; "*.xlsb")
   Do While FileName &lt;&gt; ""
   Set WorkBk = Workbooks.Open(FolderPath &amp; FileName)
    WorkBk.Application.Run _
      "'" &amp; FileName &amp; "'!auto_open"
   Set SourceRange = WorkBk.Sheets("Sheet8").Range("B14", Range("XFD14").End(XltoLeft).Offset(0,2))
    Set DestRange = Sheets("Sheet2").Range("B" &amp; NRow)
      Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
         SourceRange.Columns.Count)
    DestRange.Value = SourceRange.Value 
    NRow = NRow + DestRange.Rows.Count
   WorkBk.Close savechanges:=False
    Loop
  </code></pre>