Run-time error -2147417848 (80010108) automation error: the object invoked has disconnected from its clients

[Cashu]

Good morning guys,

Code:
Set mybook = Workbooks.Open("C:\Normal Files\Engineer Spreadsheet.xlsx")
Workbooks("Engineer Spreadsheet.xlsx").Activate
If ActiveSheet.Name = Format(Date - 2, "dd-mmm-yyyy") Then
ThisWorkbook.Sheets("ABC").Copy after:=mybook.Sheets(Format(Date - 2, "DD-MMM-YYYY"))
Else
ThisWorkbook.Worksheets("ABC").Copy after:=mybook.Worksheets(Format(Date - 1, "DD-MMM-YYYY"))
End If
ActiveSheet.Name = Format(Date, "DD-MMM-YYYY")

This automation error occurs at line ThisWorkbook.Worksheets("ABC").Copy after:=mybook.Worksheets(Format(Date - 1, "DD-MMM-YYYY"))
I have tried several ways it works fine but sometimes it throws error at this line.Any help is much appreciated.

 

[NdNoviceHlp]

Just a guess... there's only 2 digits in months, so date format is DD-MM-YYYY not DD-MMM-YYYY. HTH. Dave
ps Welcome to the Board! Please use code tags when pasting code.

 

[Cashu]

Hi MdNovice Hlp The example of this format is 08-JAN-2024.Thank you for this warm welcome.Sure will start using code tags.

 

[NdNoviceHlp]

Hi again *Cashu. Thanks for the learning and my apologies for the the uninformed "help". This probably doesn't matter, but the one line of code uses mybook.worksheets and the other mybook.sheets? I'm guessing again, but it seems likely that occasionally the sheet doesn't exist (maybe a 3 day wkend?). If all else fails, you could trial setting the sheet name before the copy/paste...

Dim DateStr As String
If ActiveSheet.Name = Format(Date - 2, "dd-mmm-yyyy") Then
DateStr = Format(Date - 2, "DD-MMM-YYYY")
ThisWorkbook.Sheets("ABC").Copy after:=mybook.Sheets(DateStr)
Else
DateStr = Format(Date - 1, "DD-MMM-YYYY")
ThisWorkbook.Worksheets("ABC").Copy after:=mybook.Worksheets(DateStr)
End If
ActiveSheet.Name = Format(Date, "DD-MMM-YYYY")

HTH. Dave

 

[Cashu]

Thank you for the suggestion.It was of great help.

NdNoviceHlp buddy.I have changed the code and it has started working.
​

​

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave