1

I have a macro that makes a backup. I want the macro to be available for all my workbook so I put it on my personal.xlsb file. But the backup is being save on the .xlsb path not on the path of the workbook I'm actualy working... Maybe it's the Application.ThisWorkbook.path code

Sub Backup()
'makes backup
    
   Dim MyDate
    MyDate = Date
    Dim myTime
    myTime = Time
    Dim TestStr As String
    TestStr = Format(myTime, "hh.mm.ss")
    Dim Test1Str As String
    Test1Str = Format(MyDate, "DD-MM-YYYY")
    Dim strFolderName As String
    Dim strFolderExists As String
    Dim path As String

   'the problem must be in the code below
    path = Application.ThisWorkbook.path
    strFolderName = path & "\Backup\"
    strFolderExists = Dir(strFolderName, vbDirectory)
    Debug.Print path
    Application.DisplayAlerts = False

    If strFolderExists = "" Then
        
        MkDir strFolderName
        path = strFolderName & Test1Str & "_" & TestStr & "_" & ActiveWorkbook.Name
        
    Else
        
        path = strFolderName & Test1Str & "_" & TestStr & "_" & ActiveWorkbook.Name
        
    End If

    ActiveWorkbook.SaveCopyAs Filename:=path
    Application.DisplayAlerts = True
    
End Sub
Max Bridge
  • 331
  • 4
  • 17
  • 1
    Yes, you're right. You're saving the `ActiveWorkbook` but you're using `ThisWorkbook.Path`. You should be using `ActiveWorkbook.Path` – Toddleson Dec 03 '21 at 16:48
  • 1
    Side Note: this line `path = strFolderName & Test1Str & "_" & TestStr & "_" & ActiveWorkbook.Name` appears in both branches of the If Statment. This line could instead be moved outside the If Statement and the `Else` branch can be removed. – Toddleson Dec 03 '21 at 16:51
  • Thanks @Toddleson That's it! Works perfectly! – Max Bridge Dec 03 '21 at 16:52

0 Answers0