Here is a sample portion of a macro that shows using the Application.OnUndo
method in operation. Simply copy/paste all the code below into a Module
(Insert/Module from the VB editor menu bar) and then run the YourMacro macro
from a worksheet. Note that setting up the macro I named UndoPaste needs to
be tailored specifically to be able to undo whatever YourMacro actually
did... there is no general Undo that can be implement... you must write the
code to undo whatever your macro does yourself... Excel/VBA will not track
it for you. As written, this code will only Undo the copy/paste operation
with the same workbook. You would have to extend the global variables (those
declared outside of a procedure) and track them yourself in order to be able
to handle multiple workbooks. Here is the code...
'******************** START OF CODE ********************
Dim DestinationCells As String
Dim DestinationData As Variant
Dim DestinationSheet As String
Sub YourMacro()
Dim SourceRange As Range, DestinationRange As Range
'
' <<Beginning Code>>
'
On Error GoTo Whoops
Set SourceRange = Application.InputBox(Prompt:="Select range to copy.", _
Title:="Select Copy Range", Type:=8)
Set DestinationRange = Application.InputBox(Prompt:="Put it where?", _
Title:="Paste Selected Range", Type:=8)
DestinationSheet = ActiveSheet.Name
DestinationCells = DestinationRange.Address
DestinationData = DestinationRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)
SourceRange.Copy DestinationRange
Application.OnUndo "Undo Paste Operation", "UndoPaste"
'
' <<Ending Code>>
'
Whoops:
End Sub
Sub UndoPaste()
Range(DestinationCells).Resize(UBound(DestinationData, 1) - _
LBound(DestinationData, 1) + 1, UBound(DestinationData, 2) - _
LBound(DestinationData, 2) + 1) = DestinationData
End Sub
'******************** END OF CODE ********************
