Error in Excel macro for taking backup of excel sheet


New Member
Feb 1, 2019
[FONT=&quot]I am looking to create backup of the current workbook.
The steps are as follows:
1. an excel file is opened
2. I am running be below code to create back up of the file
3. the file should create folder named "Export" if not already there
4. the file should create sub-folder named "Back up" in the "Export" folder if not already there
5. the file should create further sub-folder named which will be named in date format as "dd-mmm-yyyy" in the "Back up" sub folder, if not already there
6. then it should take back up of the file. the back up path should be the sub folder created at Sr.5 above (i.e. "dd-mmm-yyyy" and the file name should be time stamp (yyyy-mm-dd-hh-ss) and name of the opened file

I am using below code but it is returning error. Can you please help with this? Thanks.

Sub backupfile()
Dim ruta As String, ruta2 As String, ruta3 As String, ruta4 As String, nFile As String
nFile = Format(Date, "dd-mmm-yyyy")
ruta = Application.ActiveWorkbook.Path
ruta2 = ruta & "" & "export"
If Dir(ruta2, vbDirectory) = "" Then
MkDir ruta2
End If
ruta3 = ruta2 & "" & "Back up"
If Dir(ruta3, vbDirectory) = "" Then
MkDir ruta3
End If
ruta4 = ruta3 & "" & nFile
If Dir(ruta4, vbDirectory) = "" Then
MkDir ruta4
End If
Dim savedate
savedate = Date
Dim savetime
savetime = Time
Dim formattime As String
formattime = Format(savetime, "")
Dim formatdate As String
formatdate = Format(savedate, "DD - MM - YYYY")
Application.DisplayAlerts = False
Dim backupfolder As String
backupfolder = ruta4
ActiveWorkbook.SaveCopyAs Filename:=backupfolder & formatdate & " " & formattime & " " & ActiveWorkbook.Name
End Sub

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jeffrey Mahoney

Well-known Member
May 31, 2015
It doesn't look like you're putting backslashes in between the folder names

ruta2 = ruta & "" & "export"
should be
ruta2 = ruta & "" & "export"


Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics