One More Try To Run This Macro With A Formula

[rockyw]

I have this code..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("E3")) Is Nothing Then Macro
    Sheets("Sheet3").Select
    Range("E3:E24").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("E3:E24").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Can I make this run when a cell or some cells in a range of cells (sheet 3 E3:E24) gets filled by a formula? This works when I use a command button but I would like it to just run when the other cells get filled. I seen a lot of ideas but none work, can an IF formula make a macro run? Thanks

 

[mikerickson]

Try putting this in Sheet3's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyRange As Range
    Set keyRange = Range("E3:E24")
    On Error Resume Next
    Set keyRange = Application.Union(keyRange, keyRange.Precedents)
    On Error GoTo 0
    If Not Application.Intersect(Target, keyRange) Is Nothing Then
        With Range("E3:E24")
            ThisWorkbook.Sheets("Sheet2").Range(.Address).Value = .Value
        End With
    End If
End Sub

 

[rockyw]

I just will not run by it's self. I can run it manually but it will not fire on it's own. Thanks but I guess I will have to keep a command button for the macro. Have you ever seen this work on other workbooks? Thanks for your help.

 

[Rick Rothstein]

I just will not run by it's self. I can run it manually but it will not fire on it's own.

It sounds like you have not installed the Mike's code in the correct place. Event code does not go in the same module as macros do; rather, it goes in the worksheet module for the sheet that you want to monitor. Look over in the Project Window and locate the Sheet3 entry... double click it... that will open up a code window... put Mike's code in that code window and it will run automatically when changes are made to the cells being monitored.

 

[Norie]

What do you actually want to do?

Is this code meant to be triggered when a formula is put in E3?

Or when the result of a formula in E3 changes?

 

[rockyw]

Or when the result of a formula in E3 changes?

When the result of the formula changes in E3 thru E24. It may be every cell, it may just change in half of them depending on inventory. That's what I have been trying to say, I must not have made that clear. I paste a report on sheet 1, formulas on sheet 3 E3:E24 look for data and return it to sheet 3 E3:E24, then this macro copys and pastes sheet 3 E3:E24 and paste special (value only) in sheet 2 E3:E24. That make sence? Can this macro still be ran some how by it' self? Thanks

 

[mikerickson]

What cells on sheet1 are precedents for Sheet3!E3:E24 ?

 

[rockyw]

Again, thanks for your help and sorry if I am not clear on what I am trying to do. I have a workbook that uses 3 sheets. I run a report, this covers A-V or more, rows 1-540 or more. I copy this, open the workbook I am working on now and paste the report on sheet 1. On sheet 3 I have these formulas in E3:E24 that look for item numbers and sum the totals back to E3:E24. The formulas are as follows...

=SUM(SUMIF(Sheet1!P:P,{"651102*"},Sheet1!B:B))

I then use the macro to copy and paste the values from sheet 3, E3:E24 to Sheet 2, E3:E24. I do this because it's the only way I can keep the condistional formating working correctly on sheet 2. If I put a formula directly on sheet 2, the formating sees the formuals and will not work correctly, so I use the macro to copy, paste special the values from sheet 3 to sheet 2. I would like to just paste the report on sheet one, the formulas put the values in sheet 3, and I would like the macro to copy the values from sheet 3 to sheet 2 without any buttons or acts to run the macro. I am making this, others will use it. I'm trying to make it as simple as I can. Thanks

 

[rockyw]

The condisional formating on sheet 2 is, =E3="" I want the text to be white ( I can't see it) until a value is there, then the text turns black and I can see the discription. If I put a formula in there the condistional format sees the formula and thinks it's a value. The macro copy paste special is the only way I know to do this. Thanks

 

[mikerickson]

Put this in Sheet1's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("P:P,B:B") Is Nothing Then
        With ThisWorkbook
            .Sheets("Sheet2").Range("E3:E24")).Value = .Sheets("Sheet3").Range("E3:E24")).Value
        End With
    End If
End Sub