Backup Save in Addition to Regular Save

Vonsteiner

New Member
Joined
Apr 14, 2014
Messages
23
Greetings Everyone,

I hope everyone is well. I have userform code in place to save a file based on a cell value and to also save a backup file with the date added. Both of these work for the most part. Here is the code as I have it right now:

Code:
Dim wb As Workbook
Dim wbname As String

Set wb = ActiveWorkbook

wbname = ThisWorkbook.Path & "\" & _
    ThisWorkbook.Worksheets("Case Information").Range("A1").Value & " MTO "
    
wb.SaveAs wbname

Call BackupWorkbook
Backup Code
Code:
Dim wb2 As Workbook
Dim wbname2 As String


Set wb2 = ActiveWorkbook


wbname2 = ThisWorkbook.Path & "\Backups\" & _
    ThisWorkbook.Worksheets("Case Information").Range("A1").Value & " MTO " & _
    Format(Now(), "dd-mm-yyyy")
    
wb2.SaveAs wbname2
With the code in this order is saves both of the files in the correct place, but the backup file now has become the active workbook. If I reverse the code and have Excel save the backup first it saves both files in the Backups folder, but it does make my non-backup the active file. Is there a way to have Excel save both files correctly, but then have the non-backup file be the active workbook? Also, I have the code save the workbook after each userform using the wb.save command. Right now it would just save the non-backup. How would I include the backup being saved as well.

Michael
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,265
Office Version
2013
Platform
Windows
Hi,
Use SaveCopyAs to make backups of your workbook
[FONT=&quot]This method saves a copy of your workbook to a file but doesn't modify the open workbook in memory.

Untested but you could modify your code as follows

Rich (BB code):
[/FONT]
Rich (BB code):
Dim wb As Workbook
Dim wbname As String


Set wb = ActiveWorkbook


wbname = ThisWorkbook.Path & "\" & _
    ThisWorkbook.Worksheets("Case Information").Range("A1").Value & " MTO "
    
wb.SaveAs wbname

'pass workbook object & file path to backup code
Call BackupWorkbook(wb, wbname)
[FONT=&quot]



'make backup copy

Rich (BB code):
[/FONT]
Rich (BB code):
Sub BackupWorkbook(ByVal wb As Object, ByVal BackupFilePath As String)
    wb.SaveCopyAs BackupFilePath & " " & Format(Now(), "dd-mm-yyyy")
End Sub
[FONT=&quot]



Dave[/FONT]
 

Vonsteiner

New Member
Joined
Apr 14, 2014
Messages
23
The backup wasn't saving in the correct folder or as an Excel file. So I tweaked my original code and it works perfectly now. The 'SaveCopyAs' was the exact code I needed, Thank you Dave. I went with this to save the original and a backup:

Code:
Dim wb As Workbook
Dim wb2 As Workbook
Dim wbname As String
Dim wbname2 As String


Set wb = ActiveWorkbook
Set wb2 = ActiveWorkbook

'save file
wbname = ThisWorkbook.Path & "\" & _
    ThisWorkbook.Worksheets("Case Information").Range("A1").Value & " MTO" & ".xlsm"
    
wb.SaveAs wbname
    
'save a backup


wbname2 = ThisWorkbook.Path & "\Backups\" & _
   ThisWorkbook.Worksheets("Case Information").Range("A1").Value & " MTO " & _
    Format(Now(), "dd-mm-yyyy") & ".xlsm"


wb2.SaveCopyAs wbname2



Michael
 
Last edited:

Forum statistics

Threads
1,081,994
Messages
5,362,607
Members
400,684
Latest member
Vie

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top