Okay, so I found a solution that works. Apparently I can't use Open to open excel in excel. I have to use workbooks.open. This solution simply opens the source file, copies the relevant sheet into the current document and closes the sourcefile. problem solved. (The help document that it is based on is here, but it is a bit incorrect and needed playing with: https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open)

Sub ImportWorksheet()
    Dim sourcefile, sourceloc, sourcetab As String
    sourcefile = "space flight.xlsx"
    sourceloc = "C:\Users\moish\OneDrive\Documents\space flight.xlsx"
    sourcetab = "Bodies"
    
    Destination = ActiveWorkbook.Name
    
    Workbooks.Open sourceloc
    
    
    ActiveSheet.Name = tabname
    Sheets(sourcetab).Copy Before:=Workbooks(Destination).Sheets(1)
    
    Windows(sourcefile).Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(Destination).Activate
End Sub