Have to apply the VBA code in specific range of columns.

[Jagdev Singh]

Hi Experts,

The below code keeps the format of the copies data in general format in the sheet. It is currently applied to entire sheet. Please help me to get the range set from Column A to Column M in the sheet.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim UndoList As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error GoTo Whoa
    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
    Then GoTo LetsContinue
    '~~> Undo the paste that the user did but we are not clearing
    '~~> the clipboard so the copied data is still in memory
    Application.Undo
    If UndoList = "Auto Fill" Then Selection.Copy
    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", _
    Link:=False, DisplayAsIcon:=False
    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    On Error GoTo 0
    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Regards,
JD

 

[Luke M]

Change the beginning like so:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim UndoList As String
   
    If Intersect(Target, Sh.Range("A:M")) Is Nothing Then Exit Sub
    'Rest of code continues...

 

[Jagdev Singh]

Thanks Luke

You are the gem.

Regards,
JD