zerakh
New Member
- Joined
- Sep 13, 2020
- Messages
- 6
- Office Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- 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.
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.
Thank you for your assistance.
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.
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.