VBA to Store Copy clipboard ---> Save workbook ---> Paste as values in Selection

[ThomasA83]

Hi,

I am trying to let Excel save active workbook just before my paste as values code, however I can't figure a way to store the clipboard before the Activeworkbook.Save event, and after the event the clipboard is lost if it is consist of data from another or current excel sheet. It is no problem to do the Save and paste code if the Copy is from another application, ie. Outlook.

Any ideas?

Sub PasteasValue()

Application.EnableEvents = False
Application.ScreenUpdating = False


ActiveWorkbook.Save


Selection.PasteSpecial Paste:=xlPasteValues


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

Brgds
Thomas

 

[Yongle]

I am guessing that you are manually copying copyFrom range, selecting the pasteTo range and wanting to save active workbook before doing the actual pasting
- is that correct?
- if not, please explain

Are you copying or cutting?

Is copying and pasting happening in the same sheet in the one workbook?
- if not please explain

Possible solution
- this method should work for you IF it is practical to select the pasteTo range AFTER saving the file

Select the copyFrom range and run the macro (using a COPY of your workbook!)

Sub SelectThis()
    Dim Clip As Range, Clop As Range
'determine range to be copied
    Set Clip = Selection
'save the workbook
    ActiveWorkbook.Save
'set paste to range
    Set Clop = Application.InputBox("Paste where?", , , , , , , 8)
    Clip.Copy
    Clop.PasteSpecial Paste:=xlPasteValues
End Sub

 

[ThomasA83]

That was not what I am after.

The thing is that I want a code which paste as values, but would like to still have the possibility to undo the paste values action, but as undo is not possible after running a VBA code, then I thought the solution could be to save the Workbook just before the Paste value code, so if you want to undo the paste, then you could close the workbook and re-open the latest saved version as it was just before the paste value.

 

[Yongle]

At least I now understand what you are trying to achieve
- why don't you simply save a copy of the file before the copy/paste using

ActiveWorkbook.SaveCopyAs method

 

[ThomasA83]

Because it is a daily worklist that users are working in everyday, and if I saveas then I would end up with multiple workbooks per user.

Therefore the solution I am after is either of below 2 solutions as I see it:

Solution 1:
VBA to do store current clipboard somehow, save file and paste the stored clipboard copy data into the selection cell

Solution 2:
VBA code to undo the paste values VBA code in order to be able to undo the paste value action if need be

 

[Yongle]

Can you explain what would trigger the user to elect to revert to the pre-paste version
- is it a user decision
OR
- could it be written as a rule

Tomorrow I will provide you with solution 2
- I know a method to do that

 

[ThomasA83]

The trigger if is he mistakenly paste something wrong, then the user would like to revert to how it was just before the paste value code. Currently I have made a shortcut "Ctrl + v" to paste values only, and I have placed the code in ThisWorkbook, so it wont fire in other excel sheets.

Looking forward to hear from you

 

[Yongle]

Currently I have made a shortcut "Ctrl + v" to paste values only

The good news:
- I will post an alternative suggestion later (see bad news!)

The bad news:
- you cannot UNDO what a macro has pasted

Here is the code I promised which you can test with a manual PasteSpecial
Test in a NEW workbook

place code in SHEET module

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Handler
    If Left(Application.CommandBars("Standard").Controls("&Undo").List(1), 13) = "Paste Special" Then
        If MsgBox("Want to undo?", vbYesNo) = vbYes Then Application.Undo
    End If
Handler:
End Sub

 

[Yongle]

Amend the macro below to suit your own requirement but ...
- VBA MUST be told which range is to be copiedFrom and pastedTo and the easiest way to do that is to capture those ranges within the macro itself
- if for any reason that is not convenient, then you will need to find a different way to capture those ranges

Suggested workaround explained
- create a temporary copy of the sheet
- paste values to temporary sheet first for the user to check
- paste to original sheet ONLY if user confirms via message box
- delete temporary sheet

To test
- test on a COPY of your workbook
- put the code below in the SHEET module
- assign your shortcut to it
- run (using shortcut)
- select copyFRom and pasteTo ranges as instructed
- see if it does what you want

Sub Ctrl_V_MacroName()
[COLOR=#ff0000]'place code in SHEET module [/COLOR]
    Dim ws As Worksheet, CopyRng As Range, PasteRng As Range
[I][COLOR=#006400]'get CopyFrom and PasteTo[/COLOR][/I]
    On Error GoTo Handler
    Set CopyRng = Application.InputBox("Select CopyFrom Range and click OK", , , , , , , 8)
    Set PasteRng = Application.InputBox("Select PasteTo Cell or Range and click OK", , , , , , , 8)
    On Error GoTo 0
[I][COLOR=#006400]'create temporary sheet and paste results there[/COLOR][/I]
    Me.Copy After:=Sheets(Sheets.Count)
    Set ws = Sheets(Sheets.Count)
    ws.Name = "TEMP " & Round(Timer, 0)
    CopyRng.Copy
    ws.Range(PasteRng.Address).PasteSpecial Paste:=xlPasteValues
[COLOR=#006400][I]'ask user to confirm[/I][/COLOR]
    If MsgBox("Do you want to continue with paste?", vbYesNo) = vbYes Then PasteRng.PasteSpecial Paste:=xlPasteValues
[I][COLOR=#006400]'delete temporary sheet[/COLOR][/I]
    On Error Resume Next
    Application.DisplayAlerts = False
        ws.Delete
    Application.DisplayAlerts = True
    Me.Activate
    PasteRng.Cells(1).Select
Handler:
End Sub