title: Transfer data from multiple workbooks into Master workbook in SharePoint
body: <p>I am trying to run a macro, in an Excel document that copies values in standardised time-sheets (Other Excel Documents) and pastes them into my original macro enabled spreadsheet (Zmaster file), in SharePoint. </p>
  
  <p>The code works in my local drive but not when the master file and all other time-sheet spreadsheets are in SharePoint.</p>
  
  <pre><code>Sub LoopThroughDirectory()
  Dim MyFile As String
  Dim erow
  Dim Filepath As String
  Filepath = "//hub.bcu.ac.uk/sites/rie/cdc-work-area/Timesheet Test RESTRICTED Access"
  MyFile = Dir(Filepath)
  
  Do While Len(MyFile) &gt; 0
  
      If MyFile = "Zmaster.xlsx" Then
          Exit Sub
      End If
  
      Workbooks.Open (Filepath &amp; MyFile)
      Range("A59:AF59").Copy
      ActiveWorkbook.Close
  
      erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  
      ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow, 35))
  
      MyFile = Dir
  
  Loop
  
  Range("D1").Select
  MsgBox "Process is Complete!"
  
  End Sub
  </code></pre>
  
  <p>In the Zmaster document in SharePoint, I get the "Process is Complete" message without anything being transferred.</p>