VBA doesn't copy the AVERAGE

Sub EnterFormula()
    Worksheets("Sheet1").Range("M65").Formula = "=AVERAGE(M6:M47)"
    Range("M66").Copy Range("M67")    'On Deck becomes Prior Data
    Range("M65").Copy Range("M66")     'New data copied to On Deck area
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B6:B47")) Is Nothing Then
        Application.EnableEvents = False
        Range("K6:K47").Copy Range("L6:L47")    'On Deck becomes Prior Data
        Range("B6:B47").Copy Range("K6:K47")     'New data copied to On Deck area
        Application.EnableEvents = True
        End If
End Sub

I am good with the second section. It copies the old and new values to the ranged cells. But the first section doesn't work. Neither it is taking the average, nor copying the new value to M66 and old value to M67.

What am I doing wrong?!?!

Maybe:

Sub EnterFormula()
    Worksheets("Sheet1").Range("M65").Formula = "=AVERAGE(M6:M47)"
    Range("M67") = Range("M66").Value    'On Deck becomes Prior Data
    Range("M66") = Range("M65").Value     'New data copied to On Deck area
End Sub

Dom

1. There no need to insert the formula repetitively.

2. There no call to EnterFormula in the code that appears. If it's called from VBA, it should be in a code module, not the sheet module. If it's called as a worksheet function, it won't work regardless of where it appears.

Originally Posted by shg

1. There no need to insert the formula repetitively.

2. There no call to EnterFormula in the code that appears. If it's called from VBA, it should be in a code module, not the sheet module. If it's called as a worksheet function, it won't work regardless of where it appears.

Both fair points.

Dom

Originally Posted by Litstone

Unfortunately not

What? Does it not do what is required? Does it not work at all?

Have you followed Shg's advice about the location of the code. At the moment it will not run automatically if that is what you are expecting it to do.

A bit more info would be useful.

Dom

So it is getting more clear because my cells ranged from M6 to M47 contain formulas, too.

In tis case I have to write them too with macro. So is this a true definition?

Sub EnterFormula()
    Worksheets("Sheet1").Range("M6:M47").Formula = "=([(K6:K47)-(L6:L47)]/L6:L47)"
End Sub

So at the and it should look like that but I could make it Run:

B6:B47 called from external source.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B6:B47")) Is Nothing Then
        Application.EnableEvents = False
        Range("K6:K47").Copy Range("L6:L47")    'On Deck becomes Prior Data
        Range("B6:B47").Copy Range("K6:K47")     'New data copied to On Deck area
        Application.EnableEvents = True
    End If
End Sub
Sub EnterFormula()
    Worksheets("Sheet1").Range("M6:M47").Formula = "=([(K6:K47)-(L6:L47)]/L6:L47)"
End Sub
Sub EnterFormula()
    Worksheets("Sheet1").Range("M65").Formula = "=AVERAGE(M6:M47)"
    Range("M67").Copy Range("M66").Value    'On Deck becomes Prior Data
    Range("M66").Copy Range("M65").Value     'New data copied to On Deck area
End Sub

Kur.xlsm

I am feeling like a dummy blond crash test mannequin

The thread is marked Solved -- is it?