Try this in a standard module
Option Explicit
Sub CalcVols()
Dim LastRow As Long, ColNo As Long
Dim rngCol As Range
Dim wsSource As Worksheet, wsVolume As Worksheet
Set wsSource = Sheets("Source")
Set wsVolume = Sheets("Volumes")
With wsSource
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("S12").Resize(LastRow - 11).Formula = "=SUM(B12:R12)"
.Range("AA12").Resize(LastRow - 11).Formula = "=SUM(T12:Z12)"
End With
With wsVolume
Set rngCol = .Rows("4:4").Find(.Range("C2") * 24)
If Not rngCol Is Nothing Then
ColNo = rngCol.Column
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
.Cells(5, ColNo).Resize(LastRow - 4).Formula = "=Source!S12+Source!AA12"
.Range(.Cells(5, ColNo), .Cells(LastRow, ColNo)).Copy
.Cells(5, ColNo).PasteSpecial xlPasteValues
End If
Application.CutCopyMode = False
.Cells(4, ColNo).Select
End With
End Sub
And in the sheet module Sheets("Volumes")
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$2" Then
CalcVols
Range("C2").Select
End If
End Sub
Click on "Update Sheet" in Sheets("Volumes") B2 to run the macro
I have not put in any code to avoid over writing data entries already made as yet.
Hope this helps
Bookmarks