title: VBA Copy Worksheet to a Different Open Workbook
body: <p>I keep getting an Out of Range error when trying to copy a sheet from on workbook to another.  The original spreadsheet (Master_Data.xlsm) is what is running the vba script.  The scripts opens another spreadsheet, manipulates it, then copies the final sheet to be pasted in the Master_Data.xlsm Workbook.  </p>
  
  <pre><code>Sub Result_Scrapper()
  
  Dim wb As Workbook, ws As Worksheet, wbFile As Object
  Dim masterBook As Workbook
  Dim wsa As Worksheet
  Dim year As Integer
  
  
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set fldr = fso.GetFolder("C:\Users\Output_Spreadsheets\")
  Set masterBook = Excel.Workbooks("Master_Data.xlsm")
  
  Application.ScreenUpdating = False
  For Each wbFile In fldr.Files
  If fso.GetExtensionName(wbFile.Name) = "xlsm" Then
      Set wb = Workbooks.Open(wbFile.Path)
      'Copy sheet of interest
      ActiveSheet.Copy 'Before:=ThisWorkbook.Sheets(“A”) 'tried doing it using before statement but it also caused errors
      'paste sheet into masterBook spread--this is where the error comes
      masterBook.Sheets(Sheets.Count).Paste
  
      End If
  masterBook.Sheets("master").Name = Right([A2], 30)
  Next wbFile
  </code></pre>