How to find last 10 cells in a row and paste the average into new sheet

banana97,

This would be one way of doing it. Probably the hard way, but at least it will get you started.

Sub aaa()

Dim lLastRow As Long, lrow As Long, lSum As Long
Dim wsComp As Worksheet, wsLPM As Worksheet

Set wsComp = Sheets("Comp")
Set wsLPM = Sheets("0.3lpm")

lLastRow = wsLPM.Cells(Rows.Count, "G").End(xlUp).Row
lSum = 0

For lrow = lLastRow To lLastRow - 9 Step -1
    
    lSum = lSum + wsLPM.Cells(lrow, "G").Value
    
Next lrow

wsComp.Cells(1, 1).Value = lSum / 10

End Sub

banana97,

Try this. It is setting up a formula to be placed in cell A1 on sheet Comp. It creates a formula that sums the last 10 cells of Column G and divides by it by 10.

Sub aaa()

Dim lLastRow As Long
Dim wsComp As Worksheet, wsLPM As Worksheet

'This assigns the worksheets to a variable to make coding easier
Set wsComp = Sheets("Comp")
Set wsLPM = Sheets("0.3lpm")

'This finds the last row in Column G on sheet 0.3lpm that has a value in it.
lLastRow = wsLPM.Cells(Rows.Count, "G").End(xlUp).Row

'Creates the formula
wsComp.Range("A1").Formula = "=sum('0.3lpm'!G" & CStr(lLastRow - 9) & ":G" & CStr(lLastRow) & ")/10"

End Sub

If you want this to calculate everytime a change is made to 0.3lpm you will need to put something like this Sheet 0.3lpm object in the VB editor.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call aaa
End Sub

Hope this works for you

Banana97,

Great! I'm glad it worked. Creating a formula just took a little more thought on my end so answering your question ended up being a benefit to me.

Glad I was able to help!