VBA to insert column + sum multiple columns

[wrightyrx7]

Hi All,

I have a huge report that is run on a monthly basis. It has roughly 35 columns and 5000 rows.

Each month I have to insert a new column "G" and in that Sum/add column "L" to "AF" so the total goes into "G"

Can anyone suggest anything to help?

Thank you
Chris

 

[SteveO59L]

Try using the macro recorder. Then look at the code produced. See if you can figure it out for yourself

 

[wrightyrx7]

Hi Steve, I have just tried this and it works great. But it records the sum down all the way down to the last record which is greate. But when i run this Macro on next months report. It could have more or less rows.

How would i change this code to know to calculate every row that has data in??

Sub Pen_Pay()
'
' Pen_Pay Macro
' Macro recorded 16/09/2011 by Chris Wright
'

'
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("G1").Select
ActiveCell.FormulaR1C1 = "Pensionable Pay"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[5]:RC[25])"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G5270")
Range("G2:G5270").Select
Selection.NumberFormat = "0.00"
End Sub

Thanks

Chris

 

[SteveO59L]

To find the last used row in a column (where the 1 in (Rows.Count, 1) refers to col A

Dim LASTROW As Long

LASTROW = Cells(Rows.Count, 1).End(xlUp).Row

then change your

Selection.AutoFill Destination:=Range("G2:G5270")

to

Selection.AutoFill Destination:=Range("G2:G" & lastrow)

 

[wrightyrx7]

This works great thank you very much

I am very very new to VBA but that recorder really helps. Im learning!!! :D