1

I have a macro file that transform some raw data into a processed sheet, then I will do a few activities like archiving the previous processed file, and saving the most recently processed sheet into a new file and saving the file.

Below is the relevant code after the processing:

' Saving in the processed file in folder

   ' Save a copy of the old file as backup
   If Not Len(Dir(saveFolder & saveFile & ".xlsx")) = 0 Then

        Name saveFolder & saveFile & ".xlsx" As Sheets("SETTINGS").Cells(6, 2) & saveFile & _
            " - Backup - " & Format(Now, "YYYYMMDD HHMM") & ".xlsx"

    End If

    ' Create the new workbook
    Set destWB = Workbooks.Add

    ' Copy PROCESSED into new sheet
    ThisWorkbook.Sheets("PROCESSED").Copy Before:=destWB.Sheets(1)

At the Copy command, the below error was thrown:

Run-time error '429':

ActiveX component can't create object

I tried debugging the program and cannot seem to find the problem. The new workbook is created. I checked that it exists before the next command. I checked that the sheet involved can be moved to a fresh workbook. I have just no idea why this is not working.

Also to note, this macro works perfectly until two months ago. Now this error consistently happens. I have also tried resetting my Excel AppData in the off chance it has some effect.

Thanks a lot.

iYeo
  • 11
  • 1
  • [Possibly useful](https://stackoverflow.com/questions/30430942/error-429-activex-component-cant-create-object-when-copying-cells-to-new-work). – BigBen May 08 '20 at 01:30
  • Hmm. That's useful. I couldn't find a particular fix to the issue, but the idea of creating a new file with all the existing content made the problem go away. – iYeo May 08 '20 at 02:12

0 Answers0