Transfer data from multiple workbooks into Master workbook in SharePoint

I am trying to run in a macro in a SharePoint excel document that copies values in standardised timesheets and pastes them into a master file in SharePoint.

The code below works in my local drive but not when I export the master file in SharePoint and run it from there.

Can someone please help with amending the code below so that it works in SharePoint please:

 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) > 0
 If MyFile = "Zmaster.xlsx" Then
 Exit Sub
 End If

 Workbooks.Open (Filepath & 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

When I currently run this macro in SharePoint all I get is the "Process is Complete" message without anything actually happening in the Zmaster file.

Your help is appreciated

Thanks