VBA copy columns, paste before second to last column, fill down in subtotals table

Chenboy2

New Member
Joined
Sep 25, 2005
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi,

Please see the attached screenshot. I would like the VBA code to allow users to add new categories by inserting new categories for the budget spreadsheet by copy/pasting the last category and amounts columns before Miscellaneous and then inserting a new row in the current categories subtotals table in A1:D16, referencing the new amounts cell row heading, filling down cells in B:D.

I want this to be able to able to be repeated anytime and not the specific columns are to be referenced as the new columns to be copied will be different each time and the new rows on the category subtotals table will be different each time too.

Please let me know if you have any questions.

Thanks!

Victor
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Chenboy2

New Member
Joined
Sep 25, 2005
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Here's the XL2BB:

5-SimplifiedMonthlyBudget2020-2.xlsm
ABCDEFACADAEAF
1CategoryAmount% - Income% - ExpensesDateDescription - Description - MiscellaneousMiscellaneous
2Income$0.00#DIV/0! Jan 1
3Utilities$0.00#DIV/0!#DIV/0! Jan 2
4Groceries$0.00#DIV/0!#DIV/0! Jan 3
5Dining out$0.00#DIV/0!#DIV/0! Jan 4
6Transportation$0.00#DIV/0!#DIV/0! Jan 5
7Medical$0.00#DIV/0!#DIV/0! Jan 6
8Donations$0.00#DIV/0!#DIV/0! Jan 7
9Clothes$0.00#DIV/0!#DIV/0! Jan 8
10Personal Care$0.00#DIV/0!#DIV/0! Jan 9
11Travel$0.00#DIV/0!#DIV/0! Jan 10
12Savings$0.00#DIV/0!#DIV/0! Jan 11
130$0.00#DIV/0!#DIV/0! Jan 12
14Miscellaneous$0.00#DIV/0!#DIV/0! Jan 13
15Total Expenses$0.00#DIV/0!#DIV/0! Jan 14
16Balance$0.00 Jan 15
17 Jan 16
18 Jan 17
19 Jan 18
20 Jan 19
21 Jan 20
22 Jan 21
23 Jan 22
24 Jan 23
25 Jan 24
26 Jan 25
27 Jan 26
28 Jan 27
29 Jan 28
30 Jan 29
31 Jan 30
32 Jan 31
mini-sheet (2)
Cell Formulas
RangeFormula
AC1,AE1AC1=CONCATENATE("Description - "&AD1)
C2:C14C2='mini-sheet (2)'!$B2/$B$2
D3:D15D3='mini-sheet (2)'!$B3/$B$15
B15:C15C15=SUM(C3:C14)
A2A2='mini-sheet (2)'!$H$1
A3A3='mini-sheet (2)'!$J$1
A4A4='mini-sheet (2)'!$L$1
A5A5='mini-sheet (2)'!$N$1
A6A6='mini-sheet (2)'!$P$1
A7A7='mini-sheet (2)'!$R$1
A8A8='mini-sheet (2)'!$T$1
A9A9='mini-sheet (2)'!$V$1
A10A10='mini-sheet (2)'!$X$1
A11A11='mini-sheet (2)'!$Z$1
A12A12='mini-sheet (2)'!$AB$1
A13A13='mini-sheet (2)'!$AD$1
A14A14='mini-sheet (2)'!$AF$1
B2B2=SUM($H:$H)
B3B3=SUM($J:$J)
B4B4=SUM($L:$L)
B5B5=SUM($N:$N)
B6B6=SUM($P:$P)
B7B7=SUM($T:$T)
B8B8=SUM($V:$V)
B9B9=SUM($X:$X)
B10:B11B10=SUM($Z:$Z)
B12B12=SUM(AB:AB)
B13B13=SUM(AF:AF)
B14B14=SUM($AF:$AF)
B16B16=B2-SUM(B3:B14)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C15Other TypeColor scaleNO
 

Forum statistics

Threads
1,141,075
Messages
5,704,156
Members
421,330
Latest member
imdumb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top