Backup data for later restore (save previous values)

user_15265213 0
Viewed 67

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.

backup vba excel
2 Answers

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

Make BackupArray a 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 End statement (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 BackupArray).

user_32196130

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
user_98140690
Related