Prev: Insert a custom function in a cell by runnig a sub or through
Next: Calling Macro
From: Steven on 15 May 2010 12:13
Is there any way to call the Paste function using a macro code and with the
undo capability still intact. Everything I have tried the undo is not
available after the Paste.

Thank you,

Steven
From: FSt1 on 15 May 2010 12:24
hi
a macro skips all of the built in niceities that excel has. undo is one of
them.
see this site.
http://spreadsheetpage.com/index.php/site/tip/undoing_a_vba_subroutine/

Regards
FSt1

"Steven" wrote:

> Is there any way to call the Paste function using a macro code and with the
> undo capability still intact. Everything I have tried the undo is not
> available after the Paste.
>
> Thank you,
>
> Steven
From: Rick Rothstein on 15 May 2010 13:36
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 ********************

--
Rick (MVP - Excel)



"FSt1" <FSt1(a)discussions.microsoft.com> wrote in message
news:8CE7556F-7851-4C33-86DF-7A708E9B00BE(a)microsoft.com...
> hi
> a macro skips all of the built in niceities that excel has. undo is one of
> them.
> see this site.
> http://spreadsheetpage.com/index.php/site/tip/undoing_a_vba_subroutine/
>
> Regards
> FSt1
>
> "Steven" wrote:
>
>> Is there any way to call the Paste function using a macro code and with
>> the
>> undo capability still intact. Everything I have tried the undo is not
>> available after the Paste.
>>
>> Thank you,
>>
>> Steven

 | 
Pages: 1
Prev: Insert a custom function in a cell by runnig a sub or through
Next: Calling Macro