Can A Cell Change Run A Macro??

[rockyw]

I have a workbook where I paste a report on sheet 1, formulas on sheet 3 look for data and return it to E3:E24. This maro copies and pastes it on sheet 2 E3:E24, can I fire this macro WITHOUT me clicking anything? Can a simple cell change, any cell change in the range of sheet 3 E3:E24 make this maro run? Thnaks for any help.

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

 

[mikerickson]

If you put this in sheet3's code module, it should do what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyRange As Range
    Set keyRange = Me.Range("E3:E24")

    If Not Application.Intersect(Target, keyRange) Is Nothing Then
        Sheet2.Range(keyRange.Address).Value = keyRange.Value
    End If
End Sub

 

[rockyw]

Thank you for the help, I put that in sheet 3 Module 1? And will this still paste special? Thanks

 

[rockyw]

I tried it in Module 1 and 3, nothing happens on sheet 2. The macro will not work unless I run it.

 

[mikerickson]

In the Project Explorer, double click on Sheet3's icon. A window should appear, named something like
Workbook1.xlsm - Sheet3 (code)

That's the window to paste that event code into.

 

[rockyw]

Will it make a differance if I named the workbook? Nothing happens now, The workbook is called ProductionShellInventory

 

[mikerickson]

That would change the name of the window.
The way to insure that the code is pasted in the correct module is to use the VBEditor's Project Explorer and double click on Sheet3's icon. That icon should be in a folder named Microsoft Excel Objects.

 

[rockyw]

Yes I have it in ProductionShellInventory - Sheet 3 (code) But it will not run on it's own. I can make it run but it will not run when sheet 3 updates the data. Thanks for the help

 

[Kenneth Hobson]

Try Precedents. e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyRange As Range
    Set keyRange = Me.Range("E3:E24")

    If Not Application.Intersect(Target, keyRange.Precedents) Is Nothing Then
        Sheet2.Range(keyRange.Address).Value = keyRange.Value
    End If
End Sub

The other way to be sure that sheet code is put on the right sheet, right click the sheet's tab, View Code, and then paste.

 

[rockyw]

I beleive I have the code in the correct area. The other code did not work, it messed up the codisional formating. This code works and the formating works also. Now to make sure you understand, I am only using paist on sheet one. That's it, if I put soemthing in sheet 3 manualy, then the macro runs. Will something run the macro when the cells fill with a formula only? This all works when I use a command button to run the macro. I would just like this code as written to run when the cells fill on sheet 3. Thanks for your interest.