Stop work change event to trigger on other workbooks

[mrajesh123]

Hi All,

I'm struggling to find answer for a simple problem. I have a code that runs a macro if a user copies and pastes data onto any sheet of a workbook (lest call it - "workbook A"). The issue is, this macro runs on other workbooks when i copy paste any information. How can i prevent the macro triggering on other workbooks?

More detailed explanation:
When i copy and paste something into a new workbook (lets call it - "Book1"), excel opens the workbook name that has the macro and looks for a sheet which obviously it can't find, therefore displays Run-time error 9: Subscript out of range


Please HELP!!

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

If Not Intersect(Target, Range("$A$2:$A$10000")) Is Nothing Then
Application.OnKey "^v", "ActiveCellPaste"
Else
Application.OnKey "^v"
End If
End Sub




Module 1 Code

Sub ActiveCellPaste()


Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant
Dim x As Integer


ActiveSheet.Paste


Set lookFor = ActiveCell.Offset(0, 0)
Set rng = Sheets("2monthdata").Columns("A:B") 'this is where macro breaks and gives error message'
col = 1
'Get last Row
x = Range(Range("A1"), Range("A100000").End(xlUp)).Count
Application.EnableEvents = False
On Error Resume Next
found = Application.VLookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
Range("B" & x).Value = "New"
Range("C" & x).Value = Date
Range("D" & x).Value = UserName()
Else
Range("B" & x).Value = "Repeat"
Range("C" & x).Value = Date
Range("D" & x).Value = UserName()


End If


If ActiveCell.Offset(-1, 1).Value = "New" Then
ActiveCell.Offset(-1, 4).Select
Else
ActiveCell.Offset(0, 0).Select
End If
Application.EnableEvents = True
End Sub

 

[jsotola]

you appear to be mixing up the terms "workbook" and "worksheet"

i think that you meant to say that you want to prevent code from triggering on other worksheets

simply check worksheet name and proceed accordingly

' ThisWorkBook Code

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

    If Sh.Name <> "[COLOR=#574123]worksheet A" then exit sub   ' exits if this is now sheet("workbook A")[/COLOR]

    If Not Intersect(Target, Range("$A$2:$A$10000")) Is Nothing Then
        Application.OnKey "^v", "ActiveCellPaste"
    Else
        Application.OnKey "^v"
    End If
End Sub