Pasting a formula between two active cells - one active cell varies

Hello All,
I want to paste a formula between two active cells. However, the lower active cell varies from month to month. Therefore, the range to which I want to paste my formula also varies. I have attached an example. In the example, the formula needs to be copied from cell C5 and pasted until the cell one row above "Grand Total" in column C.
My problem is that the row (not the column) for the cell that contains "Grand Total" will vary from month to month. I would like to take this variation into account in the vba code. The formula will always be pasted in column C between cell C5 and "Grand Total", which is in column C. The other "Grand Total" in column B is not pertinent to my question. If someone could provide some code, I would be most appreciative.

Best,
Andrew

Hi !

Andrew, according to your sample workbook, you can use Excel basics
(beginner level ! Just use Macro recorder and / or read inner VBA help …)

either Copy

PHP Code: 

Sub Demo1()
    With Sheet1.[A4].CurrentRegion.Columns(3)
        .Cells(2).Copy .Cells(3).Resize(.Rows.Count - 3)
        .AutoFit
    End With
End Sub 


... or AutoFill

PHP Code: 

Sub Demo2()
    With Sheet1.[A4].CurrentRegion.Columns(3)
        .Cells(2).AutoFill .Cells(2).Resize(.Rows.Count - 2)
        .AutoFit
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

Dear xladept, I just tried your macro and it works perfectly. Thanks so much for your help!

Best,
Andrew

Dear xladept, I just tried your macro and it works perfectly. Thanks so much for your help!

Best,
Andrew

Dear Marc L., your macros work great as well. Thank you both (Marc L. and xladept) for your quick and extremely helpful responses!