Backup data for later restore (save previous values)
I am trying to figure out a way to backup data to recover it when I need it. During the UserForm initialization I am doing
'Save Backup Worksheets("Machine Format").Cells(ActiveCell.Column).EntireColumn.copy
which would be preferred if it's not the entire column but only rows with data, but I don't know how to combine this code with
Dim LastRow As Long LastRow = Range("B" & Rows.Count).End(xlUp).Row
Everything I could think of didn't work, however when I try to paste previously copied data with
'Paste Backup ActiveSheet.Paste Destination:=Worksheets("Machine Format").Cells(ActiveCell.Column)
it doesn't paste it and shows no error either. I'm not even sure if the concept of backing up data like this is an optimal or good idea.
Could someone help me, please, in solving this issue? The reason why I even need this is because I have a userform that modifies data realtime and upon closing the userform via X button I need it to cancel all the changes and I figure that I could copy the data upon userform initialization and then paste it back in if userform was closed via x button.
You can save the values in an array
Dim BackupArray() As Variant BackupArray = Worksheets("Machine Format").UsedRange.Value
And if you have to return them you can use
Worksheets("Machine Format").Cells(row, column).Value = BackupArray(row, column)
to return specific values from the backup, or revert the entire values at once:
Worksheets("Machine Format").UsedRange.Value = BackupArray
Note that this works only on changed values. If new values were added and you want to remove them too you need to clear the contents of all cells first and revert to the original range:
Dim OriginalDataRange As Range Set OriginalDataRange = Worksheets("Machine Format").UsedRange Dim BackupArray() As Variant BackupArray = OriginalDataRange.Value ' do your changes here ' revert entire backup Worksheets("Machine Format").UsedRange.ClearContents 'remove changed data including new added data OriginalDataRange.Value = BackupArray ' revert old tata
BackupArraya public variable if creating the backup and restoring does not happen in the same procedure.
Note that if you accidentally stop the entirve VBA run eg. by using the
Endstatement (do not mix up with
End Sub!) then the backup data is lost. It only persists during the runtime of VBA (or better the lifetime of the variable
Backup Column Data
Option Explicit Private bData As Variant ' Array Private brg As Range ' Column Range (object) Sub UsageExample() ' Some code... backupData ' some more code... If Something Then ' Whatever... Else retrieveData End If End Sub Sub backupData() Const wsName As String = "Machine Format" Const First As Long = 2 Const lrCol As String = "B" Dim wb As Workbook: Set wb = ThisWorkbook With wb.Worksheets(wsName) .Activate If TypeName(Selection) = "Range" Then ' Create a reference to the column range. Dim Last As Long: Last = .Range("B" & .Rows.Count).End(xlUp).Row Dim cCol As Long: cCol = Selection.Cells(1).Column Set brg = .Columns(cCol).Rows(First & ":" & Last) ' Write the values from the column range to an array. If brg.Cells.Count = 1 Then ReDim bData(1 To 1, 1 To 1): bData(1, 1) = brg.Value Else bData = brg.Value End If 'Else ' No range selected. End If End With End Sub Sub retrieveData() If Not brg Is Nothing Then Debug.Print brg.Address Debug.Print UBound(bData, 1), UBound(bData, 2) ' Write data back to range. brg.Value = bData Else Debug.Print "No range." End If End Sub