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.

2 answers

  • answered 2021-05-17 07:10 Pᴇʜ

    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).

  • answered 2021-05-17 09:10 VBasic2008

    Backup Column Data

    Option Explicit
    Private bData As Variant ' Array
    Private brg As Range ' Column Range (object)
    Sub UsageExample()
        ' Some code...
        ' some more code...
        If Something Then
            ' Whatever...
        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)
            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
                    bData = brg.Value
                End If
                ' 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
            Debug.Print "No range."
        End If
    End Sub