add column, modify cell code until last row

zerakh

New Member
Joined
Sep 13, 2020
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
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.


1600019049183.png

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.

VBA Code:
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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,423
Office Version
  1. 365
Platform
  1. Windows
Rather than a macro you could use this formula in AB9 & fill down
=SUMIFS(AG9:ZZ9,AG$7:ZZ$7,"Regular")
 

zerakh

New Member
Joined
Sep 13, 2020
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
that is so much easier....thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,423
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,490
Messages
5,548,356
Members
410,828
Latest member
A9Bosv3
Top