Forum Discussion

LesKing's avatar
LesKing
Copper Contributor
May 05, 2023

Macro to create a backup of current workbook

Hi,

I need a macro to create a backup of my current workbook into a different folder than the one the workbook is saved in.

I've found code on the internet and youtube but nothing seems to work (I assume I must be doing something wrong).

I would like the open workbook saved into "C:\backup\" folder (ideally showing - as part of the backup file name (xlsm) - the date and time the backup was created).

Can anyone please supply the code for what I assume is a requirement needed for many Excel workbooks. Thank you.

Macros and VBA

2 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 05, 2023

    LesKing 

    Try this:

    Sub CreateBackup()
    Const strFolder As String = "C:\Backup\"
    Dim strFile As String
    Dim lngPos As Long
    strFile = ActiveWorkbook.Name
    lngPos = InStrRev(strFile, ".")
    strFile = Left(strFile, lngPos - 1) & Format(Now, "_yyyymmdd_hhmmss") & Mid(strFile, lngPos)
    ActiveWorkbook.SaveCopyAs strFolder & strFile
End Sub
    • LesKing's avatar
      LesKing
      Copper Contributor
      May 05, 2023
      Thank you, Hans - you truly are a star!!!
      This worked perfectly.
      I'm a VBA newbie and I have managed quite a lot, but I still have mountains to learn, and it's great this Excel Community exists, and there are fantastic people like you to be an expert guide.
      Thanks again, Les King

Resources