title: Copying a range from all files within a folder and pasting into master workbook
body: <p>I'm fairly new to VBA so I apologize ahead of time. I've been getting involved with some complex operations and I would greatly appreciate some help or input. </p>
  
  <p>With this macro, I am trying to:</p>
  
  <ol>
  <li>Copy a specific range (2 column widths) from a specific sheet that is within all files in a given folder.</li>
  <li>Paste the range values (and formatting if possible) in a column on the already open master workbook starting at B7 and moving over 2 columns for every new document so that the pasted data does not overlap.</li>
  <li>Close files after copy/paste complete</li>
  </ol>
  
  <p>As of right now I receive a </p>
  
  <blockquote>
    <p>Run-time Error 9: Subscript out of range </p>
  </blockquote>
  
  <p>for</p>
  
  <pre><code>Workbooks("RF_Summary_Template").Worksheets("Summary").Select
  </code></pre>
  
  <p>I know this is the least of my problems, though.</p>
  
  <p>Below is my code:</p>
  
  <pre><code>Sub compile()
  
      Dim SummaryFile As String, SummarySheet As String, summaryColumn As Long
      Dim GetDir As String, Path As String
      Dim dataFile As String, dataSheet As String, LastDataRow As Long
      Dim i As Integer, FirstDataRow As Long
  
  
      '********************************
  
      RF_Summary_Template = ActiveWorkbook.Name  'summarybook
      Summary = ActiveSheet.Name     'summarysheet
  
      summaryColumn = Workbooks(RF_Summary_Template).Sheets(Summary).Cells(Columns.Count, 1).End(xlToLeft).Column + 1
      CreateObject("WScript.Shell").Popup "First, browse to the correct directory, select ANY file in the directory, and click Open.", 2, "Select Install Base File"
  
      GetDir = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
  
      If GetDir &lt;&gt; "False" Then
          Path = CurDir &amp; "\"
      Else
          MsgBox "Directory not selected"
          Exit Sub
      End If
  
      Application.ScreenUpdating = False
      dataFile = Dir(Path &amp; "*.xls")
  
      While dataFile &lt;&gt; ""
          Workbooks.Open (dataFile)
          Worksheets("Dashboard").Activate
          ActiveSheet.Range("AY17:AZ35").Copy
  
          Workbooks("RF_Summary_Template").Worksheets("Summary").Select
          Range("B8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  
          Workbooks(dataFile).Close
          summaryColumn = summaryColumn + 2
  
          dataFile = Dir()
      Wend
  
      Workbooks(RF_Summary_Template).Save
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>Thanks a million</p>