optimising & accelerating macro code

Hi peeps,

I've written the below macro to import data but it seems to be running quite slowly for what its supposed to do i.e. clear data from 6 tabs within a spreadsheet and import new external data into those 6 tabs.

I'm pretty sure my code is proper poop considering my minimal macro experience. I'd appreciate it if you could recommend improvements to my code to possibly decrease the amount of code and speed up the actual macro process time.

many thanks
Neill

Sub ImportTVRS()

' To speed up the macro run time; alert messages, screen updating & automatic calculation are disabled, this is _
  switched on again once completed.
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
            
 ' Import 6 TVR tabs (Total Variance, Coal, Gas, IPP, DE, Optimisation)
                
c00 = "\\server\reports\"
c01 = "Upstream - Total Variance Report"
c02 = Dir(c00 & c01 & "*.xls")
c03 = "Optimisation - Total Variance Report"
c04 = Dir(c00 & c03 & "*.xls")

   Sheets(Array("Total Variance", "Coal", "Gas", "IPP", "DE", "Optimisation")).Select
   Sheets("Total Variance").Activate
   [A1:Z500].ClearContents
               
   With GetObject(c00 + c02)
   ThisWorkbook.Sheets("Total Variance").[A1:Z500] = .Sheets(1).[A1:Z500].Value
    .Close True
    End With
    
    With GetObject(c00 + c02)
   ThisWorkbook.Sheets("Coal").[A1:Z500] = .Sheets(2).[A1:Z500].Value
    .Close True
    End With
    
     With GetObject(c00 + c02)
   ThisWorkbook.Sheets("Gas").[A1:Z500] = .Sheets(3).[A1:Z500].Value
    .Close True
    End With
    
     With GetObject(c00 + c02)
   ThisWorkbook.Sheets("IPP").[A1:Z500] = .Sheets(4).[A1:Z500].Value
    .Close True
    End With
    
    With GetObject(c00 + c02)
   ThisWorkbook.Sheets("DE").[A1:Z500] = .Sheets(5).[A1:Z500].Value
    .Close True
    End With
    
    With GetObject(c00 + c04)
   ThisWorkbook.Sheets("Optimisation").[A1:Z500] = .Sheets(1).[A1:Z500].Value
    .Close True
    End With
    
    Application.Goto Sheets("Rec").Cells(1, 1)
          
'  Reapply screen updating, activate automatic calculating and bring up text box once calculating completed.
            
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Process complete"
    
End Sub

im getting a run-time error '432', File name or class name not found during Automation operation on line

With GetObject(c00 + Dir(IIf(Left(sh.Name, 1) = "O", "Optimisation", "Upstream") & "- Total Variance Report.xls"))

bump, anyone else?