Via VB code, I'm attempting to locate the row that has the word 'Total' in it
(A4)which will always be in coumn A and then move to the same row in colum B
(B4) and sum that data from the row above the active cell to the top of the
column sum(B3:B1).

Column A Column B
Row1 Test1 45
Row2 Test2 51
Row3 Test3 39
Row4 Total

Here is the code I've written although it doesn't work since the word
'Total' will appear in a different row each time. Any suggestions on how to
locate the word 'Total' then move to the immediate right and sum up the data
above it?

Cells(1, 1).Select
Set rng = Range("A1:IV65400").Find(What:="Total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Range(rng, rng.End(xlToRight)).Offset(0, 1).Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUM(R[-42]C:R[-1]C)"