Need copy / paste only values macro bug fixed

Hi -

In the sheet code I have the following macro

Private Sub Worksheet_Change(ByVal Target As Range)
Location = Target.Address
If Application.CutCopyMode = False Then Exit Sub
'Allow only pastevalues
        With Application
        .CutCopyMode = False
        .ScreenUpdating = False
        .EnableEvents = False
        .Undo
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        .EnableEvents = True
        .ScreenUpdating = True
        End With
End Sub

It is from code posted by Erik Van Geit. Code works well, except...

I get an error message in the following situation:
1) Select a cell on the worksheet
2) Copy it
3) Paste into another cell
4) Note: Original selected cell still circled with dotted line
5) Select a cell that only allows specific values (drop down list)
6) Select a value from the list
7) Get error: PasteSpecial method of Range class failed

I'm quite sure it has to do with Excel being in the cut/copy mode, but I thought the first line after the 'With' fixed that.

Hi

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Location = Target.Address
If Application.CutCopyMode = False Then Exit Sub
'Allow only pastevalues
        With Application
        .CutCopyMode = False
        .ScreenUpdating = False
        .EnableEvents = False
        .Undo
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        .CutCopyMode = False
        .EnableEvents = True
        .ScreenUpdating = True
        End With
End Sub

rylo

Works great now. Thanks very much!!