Trigger Macro when data pasted into cells

[coralasiy]

I have a fully functioning macro which multiplies numbers entered it a range of cells by -1

'MINUS FIG COSTSPrivate Sub Macro43(ByVal Target As Range)
    If Intersect(Target, Range("U13:X156")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value > 0 Then Target = Target.Value * -1
End Sub

This works great if I manually enter the figures however the data will either be copy and pasted in or be pasted from another sheet in the workbook via a copy/paste macro triggered by clicking a button on the sheet which the data is pasted into (code below)

'auto populate

Private Sub Macro41(ByVal Target As Range)
   
    On Error GoTo Whoa


    If Target.Cells.CountLarge > 1 Then Exit Sub


    Dim wsI As Worksheet, wsO As Worksheet
    Dim lRow As Long, nCol As Long
    Dim sSrch As String
    Dim aCell As Range, Rng As Range


    Set wsI = ThisWorkbook.Sheets("Schedule")
    Set wsO = ThisWorkbook.Sheets("Tenancy Detail")


    application.EnableEvents = False


    If Not Intersect(Target, Range("A6:AE6")) Is Nothing Then
        sSrch = Cells(6, Target.Column).Value


        Set aCell = wsI.Rows(6).Find(What:=sSrch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)


        If Not aCell Is Nothing Then
            nCol = aCell.Column


            lRow = wsI.Cells(wsI.Rows.Count, nCol).End(xlUp).Row


            Set Rng = wsI.Range(wsI.Cells(7, nCol), wsI.Cells(lRow, nCol))
        End If


        If Not Rng Is Nothing Then
            Range(Cells(7, Target.Column), Cells(Rows.Count, Target.Column)).ClearContents
            Rng.Copy
            Cells(7, Target.Column).PasteSpecial xlPasteValues
        End If
    End If


Letscontinue:
    application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
Exit Sub
End Sub

Both codes are worksheet_change events however as there are multiple on this sheet they are run under one change event:

Private Sub Worksheet_Change(ByVal Target As Range)    Macro41 Target
    Macro42 Target
    Macro43 Target
.....etc

I am completely unable to find a working way to do this without recording a macro in which i click on every single cell and hitting enter in order to trigger the macro on that individual cell. I would like this to happen automatically when the cells are populated with pasted data.

A final note, not all cells will be populated all of the time so it is not possible to trigger the macro by detecting a change in value of one specific cell.

Any suggestions would be greatly appreciated!

Thanks!

 

[JLGWhiz]

If you want to use the sheet change event to trigger the macro that calls the other macros, then put the 'Private Sub Worksheet_Change' macro in the sheet code module of the sheet where the changes you want to trigger the code will occur. Take the 'Private designation off of the other macros and make them regular Sub procedures which should then be placed in the standard module 1. Procedures cannot be called from a Workbook, Worksheet or Userform code module. They must be called from one of the numbered code modules which are all public.

 

[coralasiy]

Thank you for your fast response,

I have moved the sub procedures to module 1, unfortunately there is no change, I am still only able to trigger the multiplication of data by manually inputing data, clicking on the cell and hitting enter or pasting one cell at a time. Do you have any suggestions to get this to run when data is pasted into multiple cells?

I have been playing around with variations of

 If Target.Count > 1 Then Exit Sub

but nothing has worked as of yet

Thank you in advance

 

[mumps]

The Worksheet_Change event will not be triggered by the results of a formula or of a paste in a cell. The cell change must be done manually.

 

[JLGWhiz]

The Worksheet_Change event will not be triggered by the results of a formula or of a paste in a cell. The cell change must be done manually.

Are you sure about the paste action, Mumps? I know it will not trigger on calculations.

 

[coralasiy]

Thanks for the responses,

Does anyone have a suggestion how to edit the formula so that pasting multiple values triggers the code to be carried out?

Mumps, the code is currently working when I paste a single value into a cell, however does not work when multiple values are pasted.

Perhaps a change in the syntax if worksheet_change is not the correct one to use?

 

[mumps]

My apologies JLGWhiz. You are absolutely correct. I'm not sure what I was thinking at the time.
@coralasiy: I believe that you have to loop through each cell in the target. Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("U13:X156")) Is Nothing Then Exit Sub
    Dim myCell As Range
    Application.EnableEvents = False
    For Each myCell In Target
        If myCell.Value > 0 Then myCell = myCell * -1
    Next myCell
    Application.EnableEvents = True
End Sub

 

[coralasiy]

Absolutely perfect mumps! Works a treat, thank you very much for your assistance!

 

[mumps]

Glad to help.