Inherited code - VBA to copy multiple sheets from workbooks on Sharepoint

[MrsMac]

Hi
I was hoping someone could help me please. I have been asked by my bosses to complete a macro started by someone else as I'm 'a bit IT'. I was a COBOL programmer in a previous life and that was 7 year ago, so have no idea about VBA code! Apologies if my questions are very basic. I would normally try and figure it out but as I have to have this completed by tomorrow, I'm really struggling. I have tried a few things from this forum but nothing seems to work properly (my fault).

What I need to do is:

I have 3 files on the sharepoint - The control file has the Macro button on and it needs to read through all the sheets in WB2 and WB3 and copy rows A2-last row from each sheet into a single sheet on the control file (MergeSheet). There is an unknown number of sheets in each WB2 and 3, and the sheet names can change. Columns A-Y only.

At the beginning of the macro, I delete and re-add 'MergeSheet' , then copy a heading line in and all this works ok.
I also think my loop will work ok but what I'm struggling with is how to grab the sheets from WB2 and 3 without opening the files and swapping between active workbooks to copy from one, activate the control file then paste into there then re-activate WB2 or 3, whichever I'm looping through.

If any of you fabulous people could have a look please, I'd appreciate your time Thanks

My code is a bit of a mess:

Sub CombineData()
 Dim Sht As Worksheet
 Dim LastRow As Long
 Dim HPCFile As Workbook
 Dim FRFile As Workbook
 Dim ControlFile As Workbook
  
 Set HPCFile = Workbooks.Open("[URL]https://sharepoint.com/teams/EUQLT/Trackers/2018[/URL] Cost of Quality Defects/2018 European CoQD - HC & PC.xlsm")
 Set FRFile = Workbooks.Open("[URL]https://sharepoint.com/teams/EUQLT/Trackers/2018[/URL] Cost of Quality Defects/2018 European CoQD - Foods&Refreshments.xlsm")
 Set ControlFile = Workbooks.Open("[URL]https://sharepoint.com/teams/EUQLT/Trackers/2018[/URL] Cost of Quality Defects/2018 European CoQD - Master.xlsm")
 
 ActiveWorkbook.Activate
  ' Delete the summary worksheet if it exists.
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("MergeSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
   ' Add a worksheet with the name "MergeSheet"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "MergeSheet"
  
   ActiveWorkbook.Worksheets("Control Sheet").Select
    Range("A1", "Y1").Copy
    Sheets("MergeSheet").Select
    Range("A1", "Y1").Select
    ActiveSheet.Paste
    HPCFile.Activate
    
  'Workbooks("[URL]https://sharepoint.com/teams/EUQLT/Trackers/2018[/URL] Cost of Quality Defects/2018 European CoQD - HC & PC.xlsm").Activate
    
    For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Range("A2").Value <> "" Then
       Sht.Select
       LastRow = Range("A65536").End(xlUp).Row
       Range("A2", Cells(LastRow, "Y")).Copy
       Windows("[URL]https://sharepoint.com/teams/EUQLT/Trackers/2018[/URL] Cost of Quality Defects/2018 European CoQD - Master.xlsm").Activate
       Sheets("MergeSheet").Select
       Range("A65536").End(xlUp).Offset(1, 0).Select
       ActiveSheet.Paste
       Workbooks("[URL]https://sharepoint.com/teams/EUQLT/Trackers/2018[/URL] Cost of Quality Defects/2018 European CoQD - HC & PC.xlsm").Activate
       Sht.Select
    Else
    End If
    
  '  ActiveWorkbook.Save
  '  Sheets("MergeSheet").Select
  '  Next Sht
    
' For Each Sht In ActiveWorkbook.Worksheets
' If Sht.Name <> "Control Sheet" And Sht.Name <> "MergeSheet" And Sht.Name <> "Instructions" And Sht.Range("A2").Value <> "" Then
'    Sht.Select
'    LastRow = Range("A65536").End(xlUp).Row
'    Range("A2", Cells(LastRow, "Y")).Copy
'    Sheets("MergeSheet").Select
'    Range("A65536").End(xlUp).Offset(1, 0).Select
'    ActiveSheet.Paste
'    Sht.Select
' Else
'
' End If
 
 Sheets("MergeSheet").Select
 Next Sht

 End Sub