automate column total 1

[MikeCDPQ]

I am totally green when it comes to VBA. Got a new job and have to take some existing code and modify it.

The script takes a worksheet with +- 25000 lines and divides it in about 300 new sheets. On each sheet, I need to have a total. I created a macro that finds the last cell, move down one and sums up the values above. That part of the script is as follows:
Application.Goto Reference:="R7C2"
Application.Goto Reference:="R7C7"
Selection.End(xlDown).Select
Range("G460&quot.Select
ActiveCell.FormulaR1C1 = "=SUM(R[-456]C:R[-1]C)"
Range("G461&quot.Select

The problem is with the SUM R[-456]. I want to change that part because from one sheet to the next, there is never the same number of line. How do I make it dynamic.

I desperately need help as I am not even familiar with the VBA sytax yet.

Thank to anyone who is willing to give me a hint.

 

[chandlm]

Mike,

you could try something like :-


sub sum_after_last_row()
LR = Range("A65536&quot.End(xlUp).Row
Range("a" & LR + 1).FormulaLocal = "=SUM(a1:a" & LR & &quot"
end sub


This is assuming that the column to be summed is column a. You should adjust it to suit your worksheet.

HTH

Matt

 

[MikeCDPQ]

Thanks,

It is a simple code and very effective it you insert the DIM LR as variant line ))

Thanks a bunch.

 

[chandlm]

Thanks for the Star, Glad it helped.

Matt