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