Excel VBA Error 1004 while saving file

Question

Good afternoon, I am trying to create an automatic backup while opening an excel spreadsheet, however an error occurs saying that I could not find the file:

I’ve hidden some parts of the way for privacy reasons, follow the code below:

Sub Autobackup()
'
' Autobackup Macro
'

'
    Dim path As String, name As String, data As String, ext As String
    path = "https://site.sharepoint.com/sites/Gestao/Documentos Compartilhados/General/BACKUP Banco de Dados/"
    name = " Banco de Dados de Contratos"
    data = Format(Date, "ww.yyyy")
    ext = ".xlsm"

     ActiveWorkbook.SaveCopyAs Filename:= _
        path & "Backup Semana " & data & name & ext

End Sub

The strange thing is that this error only occurs when I use the "Savecopyas" method. When I use the "Saveas" method, Excel can find the path and save the file:

But I don’t want to save the spreadsheet elsewhere, just make a copy of it.

Note that the code below is exactly the same as above, what changes are only the methods of the object "Activeworkbook"

Sub Autobackup()
'
' Autobackup Macro
'

'
    Dim path As String, name As String, data As String, ext As String
    path = "https://site.sharepoint.com/sites/Gestao/Documentos Compartilhados/General/BACKUP Banco de Dados/"
    name = " Banco de Dados de Contratos"
    data = Format(Date, "ww.yyyy")
    ext = ".xlsm"

     ActiveWorkbook.SaveAs Filename:= _
        path & "Backup Semana " & data & name & ext

End Sub

That is, the way is right, but for some reason, Excel cannot save a copy. Can anyone explain to me why?

Answer 1

all good? I’ve had this problem. This error happens when VBA "does not think" which spreadsheet is the right one. Try replacing Activeworkbook with Thisworkbook that will solve!