Auto Paste values

[Billdub417]

Hello,

I use the following code so that when people paste info into this workbook, it only pastes values.

However, this doesn't work when pasting from 1 workbook to another (as paste values is not an option)

Is there a way to still get it to paste the data in?

thanks,

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 Dim UndoString As String, srce As Range
    On Error GoTo err_handler
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
    If UndoString = "Auto Fill" Then
        Set srce = Selection
        srce.Copy
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
    Else
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[MARK858]

However, this doesn't work when pasting from 1 workbook to another (as paste values is not an option)

Why is pastespecial values not an option?

Application.SendKeys "{ESC}"

Why are you using SendKeys? if it is just to come out of copy mode use

Application.CutCopyMode = False

 

[Billdub417]

Why is pastespecial values not an option?

Application.SendKeys "{ESC}"

Why are you using SendKeys? if it is just to come out of copy mode use

Application.CutCopyMode = False

Hello, not sure why using send keys

Just seems to randomly sometimes not let me use paste values?? Could it be an add-in issue?

 

[MARK858]

Could be but more likely that you are activating/selecting the 2nd workbook and taking it out of copy mode.
The basic code is the Test1 macro in Book1 in the link below, the other option is the Test2 macro which doesn't use copy mode.
Open both workbooks in the links below and run the macro's in Book1 (Book2 is just a blank workbook).

Book1.xlsb | Powered by Box

app.box.com

Book2.xlsb | Powered by Box

app.box.com

 

[Billdub417]

Could be but more likely that you are activating/selecting the 2nd workbook and taking it out of copy mode.
The basic code is the Test1 macro in Book1 in the link below, the other option is the Test2 macro which doesn't use copy mode.
Open both workbooks in the links below and run the macro's in Book1 (Book2 is just a blank workbook).

Book1.xlsb | Powered by Box

app.box.com

Book2.xlsb | Powered by Box

app.box.com

ok, i think ive narrowed it down a bit further... rather than the above code, it seems to be the following that is cancelling copy mode

Is there a way to avoid this, apart from just showing ribbons all the time? (Ribbons dont show in this workbook but would in other workbooks)

Private Sub Workbook_Deactivate()

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"

End Sub

 

[MARK858]

If you use the 2nd code that I posted you aren't using Copy mode so it isn't an issue.

 

[Billdub417]

If you use the 2nd code that I posted you aren't using Copy mode so it isn't an issue.

OK yes, so in the situation I am using it, it would be like Book2 copying from book1 (where the user selects book1)

Can your code be amended to this?