add column, modify cell code until last row

[zerakh]

I have been trying to figure out how to get a macro/vba script to work, but just can't seem to get all parts to work....

I have the following image from my worksheet with a few buttons.

Each Row represents a project type per person (1 person can be on 2 different funded projects). Each row has the same formula all the way down until the last row.
Column AB, Row 9 has a cell formula: =Sum(AG9, AJ9, AM9)
Column AC, Row 9 has a cell formula: =SUM(AH9, AK9, AN9)

It's possible that I get multiple invoices (more than 3) and need to be able to make it easier to add invoice columns. My button (Add Invoice Columns) needs to add a copy of one of the existing invoice columns (AL, AM, & AN) and paste it into the series. But the formulas in the cells AB9 & AC9 need to be updated to include the new column(s). This also needs to continue for the length of column AB & AC until the last used row.

I've tried recording a macro, but it just doesn't seem to be going how I'd like it or how to add in the last row automatically (depending on how many employees I have, the rows will keep adding up). I figure it could use something like: for each row, do something.

Sub AddInvoiceColumn()

    Columns("AL:AN").Select
    Range("AN1").Activate
    Selection.Copy
    Selection.Insert Shift:=xlToRight
    Range("AB9").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(RC[5],RC[8],RC[14],RC[11])"
    Range("AC9").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(RC[5],RC[8],RC[11],RC[14])"
    Range("AB10").Select
End Sub

Thank you for your assistance.

 

[zerakh]

here is a link to the image posted above. The rest of the file was removed.
http://www.omilotdesigns.com/excel/invoice_test.xlsm

thanks again.

 

[Fluff]

Rather than a macro you could use this formula in AB9 & fill down
=SUMIFS(AG9:ZZ9,AG$7:ZZ$7,"Regular")

 

[zerakh]

that is so much easier....thank you!

 

[Fluff]

You're welcome & thanks for the feedback.