Help - VBA code inserting columns and keeping formulas

P

Prish

Board Regular
Joined
Mar 30, 2016
Messages
91
Hi,

Please assist me. I have attached a sample file.
I am using the following code to insert a column and copy formulas.

Code: 
Sub InsertColumn()

    ActiveCell.Offset(-1, 0).EntireColumn.Insert
    ActiveCell.EntireColumn.Copy ActiveCell.Offset(-1, 0).EntireColumn
    
End Sub

This inserts a column but does not include the formulas in the header row and total row. Also the total column does not include the new column in it's sum calculation. Please help me, I greatly appreciate it.

The code I have for inserting a row works fine however it copies the row above - I would like it to copy only the formulas.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Prish said:
Hi,

Please assist me. I have attached a sample file
I am using the following code to insert a column and copy formulas.
.......
This inserts a column but does not include the formulas in the header row and total row. Also the total column does not include the new column in it's sum calculation. Please help me, I greatly appreciate it.
The code I have for inserting a row works fine however it copies the row above - I would like it to copy only the formulas.
Click to expand...

Hi Prisch,
Having sample data to work with like you supplied makes it a lot easier to “see” what is going on, thanks, :)

If we can see screen shots in the Thread showing the “Before” and “after” or “what you have initially” and “what you want after the code runs” is even better.

I will have a go for you to see if i can see what you want first, then take it from there:
( To Put screen shots in a forum post. Please check out the various links in my signature when you have the time. ( BTW. I am using the Forum Tools RoryA Add-In ) )
_.......................

So take the Column Insert first.

This what you have:

Using Excel 2007
[Table="width:, class:head"][tr=bgcolor:skyblue][th]Row\Col[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
4​
[/td][td=bgcolor:#E2EFD9]Salesperson[/td][td=bgcolor:#E2EFD9]
Apr 16​
[/td][td=bgcolor:#E2EFD9]
=EDATE(C4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(D4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(E4,1)​
[/td][td=bgcolor:#E2EFD9]
Total​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
5​
[/td][td]Staff 1[/td][td]
500.00​
[/td][td]
200.00​
[/td][td]
100.00​
[/td][td]
700.00​
[/td][td]
=SUM(C5:F5)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
6​
[/td][td]Staff 2[/td][td]
450.00​
[/td][td]
500.00​
[/td][td]
600.00​
[/td][td]
200.00​
[/td][td]
=SUM(C6:F6)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
7​
[/td][td]Staff 3[/td][td]
300.00​
[/td][td]
150.00​
[/td][td]
300.00​
[/td][td]
400.00​
[/td][td]
=SUM(C7:F7)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
8​
[/td][td]Total[/td][td]
=SUM(C5:C7)​
[/td][td]
=SUM(D5:D7)​
[/td][td]
=SUM(E5:E7)​
[/td][td]
=SUM(F5:F7)​
[/td][td]
=SUM(C8:F8)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
10​
[/td][td=bgcolor:#FFF3CB]Expenses[/td][td=bgcolor:#FFF3CB]
Apr 16​
[/td][td=bgcolor:#FFF3CB]
=EDATE(C10,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(D10,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(E10,1)​
[/td][td=bgcolor:#FFF3CB]
Total​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
11​
[/td][td]category 1[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
=SUM(C11:F11)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
12​
[/td][td]category 2[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
=SUM(C12:F12)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
13​
[/td][td]category 3[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
=SUM(C13:F13)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
14​
[/td][td]Total[/td][td]
=SUM(C11:C13)​
[/td][td]
=SUM(D11:D13)​
[/td][td]
=SUM(E11:E13)​
[/td][td]
=SUM(F11:F13)​
[/td][td]
=SUM(C14:F14)​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet1[/td][/tr][/table]
_......

Would this be what you want, or have i missed something?

Using Excel 2007
[Table="width:, class:head"][tr=bgcolor:skyblue][th]Row\Col[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
4​
[/td][td=bgcolor:#E2EFD9]Salesperson[/td][td=bgcolor:#E2EFD9]
Apr 16​
[/td][td=bgcolor:#E2EFD9]
=EDATE(C4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(D4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(E4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(F4,1)​
[/td][td=bgcolor:#E2EFD9]
Total​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
5​
[/td][td]Staff 1[/td][td]
500.00​
[/td][td]
200.00​
[/td][td]
100.00​
[/td][td]
700.00​
[/td][td][/td][td]
=SUM(D5:G5)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
6​
[/td][td]Staff 2[/td][td]
450.00​
[/td][td]
500.00​
[/td][td]
600.00​
[/td][td]
200.00​
[/td][td][/td][td]
=SUM(D6:G6)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
7​
[/td][td]Staff 3[/td][td]
300.00​
[/td][td]
150.00​
[/td][td]
300.00​
[/td][td]
400.00​
[/td][td][/td][td]
=SUM(D7:G7)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
8​
[/td][td]Total[/td][td]
=SUM(C5:C7)​
[/td][td]
=SUM(D5:D7)​
[/td][td]
=SUM(E5:E7)​
[/td][td]
=SUM(F5:F7)​
[/td][td]
=SUM(G5:G7)​
[/td][td]
=SUM(D8:G8)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
10​
[/td][td=bgcolor:#FFF3CB]Expenses[/td][td=bgcolor:#FFF3CB]
Apr 16​
[/td][td=bgcolor:#FFF3CB]
=EDATE(C10,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(D10,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(E10,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(F10,1)​
[/td][td=bgcolor:#FFF3CB]
Total​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
11​
[/td][td]category 1[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td][/td][td]
=SUM(D11:G11)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
12​
[/td][td]category 2[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td][/td][td]
=SUM(D12:G12)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
13​
[/td][td]category 3[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td][/td][td]
=SUM(D13:G13)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
14​
[/td][td]Total[/td][td]
=SUM(C11:C13)​
[/td][td]
=SUM(D11:D13)​
[/td][td]
=SUM(E11:E13)​
[/td][td]
=SUM(F11:F13)​
[/td][td]
=SUM(G11:G13)​
[/td][td]
=SUM(D14:G14)​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet1[/td][/tr][/table]
_..................................................................

Similarly is this what you would want for inserting a row?

Using Excel 2007
[Table="width:, class:head"][tr=bgcolor:skyblue][th]Row\Col[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
4​
[/td][td=bgcolor:#E2EFD9]Salesperson[/td][td=bgcolor:#E2EFD9]
Apr 16​
[/td][td=bgcolor:#E2EFD9]
=EDATE(C4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(D4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(E4,1)​
[/td][td=bgcolor:#E2EFD9]
Total​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
5​
[/td][td]Staff 1[/td][td]
500.00​
[/td][td]
200.00​
[/td][td]
100.00​
[/td][td]
700.00​
[/td][td]
=SUM(D5:F5)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
6​
[/td][td]Staff 2[/td][td]
450.00​
[/td][td]
500.00​
[/td][td]
600.00​
[/td][td]
200.00​
[/td][td]
=SUM(D6:F6)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
7​
[/td][td]Staff 3[/td][td]
300.00​
[/td][td]
150.00​
[/td][td]
300.00​
[/td][td]
400.00​
[/td][td]
=SUM(D7:F7)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
=SUM(D8:F8)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
9​
[/td][td]Total[/td][td]
=SUM(C6:C8)​
[/td][td]
=SUM(D6:D8)​
[/td][td]
=SUM(E6:E8)​
[/td][td]
=SUM(F6:F8)​
[/td][td]
=SUM(D9:F9)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
11​
[/td][td=bgcolor:#FFF3CB]Expenses[/td][td=bgcolor:#FFF3CB]
Apr 16​
[/td][td=bgcolor:#FFF3CB]
=EDATE(C11,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(D11,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(E11,1)​
[/td][td=bgcolor:#FFF3CB]
Total​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
12​
[/td][td]category 1[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
=SUM(D12:F12)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
13​
[/td][td]category 2[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
=SUM(D13:F13)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
14​
[/td][td]category 3[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
=SUM(D14:F14)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
15​
[/td][td]Total[/td][td]
=SUM(C12:C14)​
[/td][td]
=SUM(D12:D14)​
[/td][td]
=SUM(E12:E14)​
[/td][td]
=SUM(F12:F14)​
[/td][td]
=SUM(D15:F15)​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet1[/td][/tr][/table]
-....

And so for Both.....

Using Excel 2007
[Table="width:, class:head"][tr=bgcolor:skyblue][th]Row\Col[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
4​
[/td][td=bgcolor:#E2EFD9]Salesperson[/td][td=bgcolor:#E2EFD9]
Apr 16​
[/td][td=bgcolor:#E2EFD9]
=EDATE(C4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(D4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(E4,1)​
[/td][td=bgcolor:#E2EFD9]
=EDATE(F4,1)​
[/td][td=bgcolor:#E2EFD9]
Total​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
5​
[/td][td]Staff 1[/td][td]
500.00​
[/td][td]
200.00​
[/td][td]
100.00​
[/td][td]
700.00​
[/td][td][/td][td]
=SUM(D5:G5)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
6​
[/td][td]Staff 2[/td][td]
450.00​
[/td][td]
500.00​
[/td][td]
600.00​
[/td][td]
200.00​
[/td][td][/td][td]
=SUM(D6:G6)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
7​
[/td][td]Staff 3[/td][td]
300.00​
[/td][td]
150.00​
[/td][td]
300.00​
[/td][td]
400.00​
[/td][td][/td][td]
=SUM(D7:G7)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
=SUM(D8:G8)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
9​
[/td][td]Total[/td][td]
=SUM(C6:C8)​
[/td][td]
=SUM(D6:D8)​
[/td][td]
=SUM(E6:E8)​
[/td][td]
=SUM(F6:F8)​
[/td][td]
=SUM(G6:G8)​
[/td][td]
=SUM(D9:G9)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
11​
[/td][td=bgcolor:#FFF3CB]Expenses[/td][td=bgcolor:#FFF3CB]
Apr 16​
[/td][td=bgcolor:#FFF3CB]
=EDATE(C11,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(D11,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(E11,1)​
[/td][td=bgcolor:#FFF3CB]
=EDATE(F11,1)​
[/td][td=bgcolor:#FFF3CB]
Total​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
12​
[/td][td]category 1[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td][/td][td]
=SUM(D12:G12)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
13​
[/td][td]category 2[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td][/td][td]
=SUM(D13:G13)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
14​
[/td][td]category 3[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td][/td][td]
=SUM(D14:G14)​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:skyblue]
15​
[/td][td]Total[/td][td]
=SUM(C12:C14)​
[/td][td]
=SUM(D12:D14)​
[/td][td]
=SUM(E12:E14)​
[/td][td]
=SUM(F12:F14)​
[/td][td]
=SUM(G12:G14)​
[/td][td]
=SUM(D15:G15)​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet1[/td][/tr][/table]

Alan.
 
Upvote 0
DocAElstein said:
I will have a go for you to see if i can see what you want first, then take it from there:
Alan.
Click to expand...

Many thanks Alan, this is exactly what i want.

I would also prefer if I click the insert column, it will insert column to the left of total column regardless of which cell is active. Similarly the same with insert row with a input box asking where to insert, sales or expense.

Thanks for taking the time.
 
Upvote 0
VBA Inserting columns and keeping formulas in Table Ranges

Hi,
OK., I will give it a go
Some questions,

_1) Does the data ( the Total Tables Area ) always start at B4? ( In other words will Top Left "Salesperson" Table always be at B4?

_2) Is this table the only thing that will be in this Worksheet

_3) What is your general level of VBA Knowledge?

_4) Would this Worksheet always be the first one in the File, ( First tab counting from the left ) ? and / or is it always called sheet1? ( If not then what is it likely to be called ? )

_5) Can I take it that these words ( always so spelt ) will always be at Top left of the Tables, so that I can take them as the table "Names" for referrence:
Salesperson
and
Expenses

Alan
 
Last edited:
Upvote 0
Re: VBA Inserting columns and keeping formulas in Table Ranges

DocAElstein said:
Hi,
OK., I will give it a go
Some questions,

_1) Does the data ( the Total Tables Area ) always start at B4? ( In other words will Top Left "Salesperson" Table always be at B4? ​correct

_2) Is this table the only thing that will be in this Worksheet yes, for now (might add other groups later)

_3) What is your general level of VBA Knowledge? very basic, mostly record and learn

_4) Would this Worksheet always be the first one in the File, ( First tab counting from the left ) ? and / or is it always called sheet1? ( If not then what is it likely to be called ? ) only worksheet named "Budget"

_5) Can I take it that these words ( always so spelt ) will always be at Top left of the Tables, so that I can take them as the table "Names" for referrence: correct
Salesperson
and
Expenses

Alan
Click to expand...

Answers in Red.
 
Upvote 0
Re: VBA Inserting columns and keeping formulas in Table Ranges

OK ,
got all that last info, no problem there,.. i think..
_. just one thing..

Prish said:
.......
I would also prefer if I click the insert column, it will insert column to the left of total column regardless of which cell is active. Similarly the same with insert row with a input box asking where to insert, sales or expense.....
Click to expand...
Do you always add a column in both tables, or should that also just be done on the selected table, sales or expense ?
 
Upvote 0
Re: VBA Inserting columns and keeping formulas in Table Ranges

DocAElstein said:
Do you always add a column in both tables, or should that also just be done on the selected table, sales or expense ?
Click to expand...

Column should be added to both sections simultaneously.
 
Upvote 0
Re: VBA Inserting columns and keeping formulas in Table Ranges

Hi Prisch,

I think i have some final codes for you and will drop them off for Today or early tomorrow..

But in the mean time I think we may have overlooked something....:rofl: :eeek:

Please review again carefully the screen shots I did for you in Post #2. I think they may be in error.

Looking at the “After” shots, I think the formulas are in error as they are missing some first rows and and first columns..
As example...
When data columns extend to G, the sum of say
=SUM(C5:F5)
Changes to
=SUM(D5:G5)
When in fact it should correctly be
=SUM(C5:G5)

And so on.

I see this is the result of the relative range references. It would be quite complicated for the code to allow for that, ( i think :confused: )

I suggest that the Spreadsheet should be modified to have a few absolute references such as the following

The before should be modified such

Using Excel 2007
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr][tr][td]
4​
[/td][td="bgcolor:#E2EFD9"]Salesperson[/td][td="bgcolor:#E2EFD9"]
Apr 16​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(C4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(D4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(E4,1)​
[/td][td="bgcolor:#E2EFD9"]
Total​
[/td][/tr]
[tr][td]
5​
[/td][td]Staff 1[/td][td]
500.00​
[/td][td]
200.00​
[/td][td]
100.00​
[/td][td]
700.00​
[/td][td]
=SUM($C5:F5)​
[/td][/tr]
[tr][td]
6​
[/td][td]Staff 2[/td][td]
450.00​
[/td][td]
500.00​
[/td][td]
600.00​
[/td][td]
200.00​
[/td][td]
=SUM($C6:F6)​
[/td][/tr]
[tr][td]
7​
[/td][td]Staff 3[/td][td]
300.00​
[/td][td]
150.00​
[/td][td]
300.00​
[/td][td]
400.00​
[/td][td]
=SUM($C7:F7)​
[/td][/tr]
[tr][td]
8​
[/td][td]Total[/td][td]
=SUM(C$5:C7)​
[/td][td]
=SUM(D$5:D7)​
[/td][td]
=SUM(E$5:E7)​
[/td][td]
=SUM(F$5:F7)​
[/td][td]
=SUM($C8:F8)​
[/td][/tr]
[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td="bgcolor:#FFF3CB"]Expenses[/td][td="bgcolor:#FFF3CB"]
Apr 16​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(C10,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(D10,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(E10,1)​
[/td][td="bgcolor:#FFF3CB"]
Total​
[/td][/tr]
[tr][td]
11​
[/td][td]category 1[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
=SUM($C11:F11)​
[/td][/tr]
[tr][td]
12​
[/td][td]category 2[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
=SUM($C12:F12)​
[/td][/tr]
[tr][td]
13​
[/td][td]category 3[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
=SUM($C13:F13)​
[/td][/tr]
[tr][td]
14​
[/td][td]Total[/td][td]
=SUM(C$11:C13)​
[/td][td]
=SUM(D$11:D13)​
[/td][td]
=SUM(E$11:E13)​
[/td][td]
=SUM(F$11:F13)​
[/td][td]
=SUM($C14:F14)​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Budget[/td][/tr][/table]


Then running the codes i have done for you give results such as the following

For a row addition

Using Excel 2007
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr][tr][td]
4​
[/td][td="bgcolor:#E2EFD9"]Salesperson[/td][td="bgcolor:#E2EFD9"]
Apr 16​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(C4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(D4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(E4,1)​
[/td][td="bgcolor:#E2EFD9"]
Total​
[/td][/tr]
[tr][td]
5​
[/td][td]Staff 1[/td][td]
500.00​
[/td][td]
200.00​
[/td][td]
100.00​
[/td][td]
700.00​
[/td][td]
=SUM($C5:F5)​
[/td][/tr]
[tr][td]
6​
[/td][td]Staff 2[/td][td]
450.00​
[/td][td]
500.00​
[/td][td]
600.00​
[/td][td]
200.00​
[/td][td]
=SUM($C6:F6)​
[/td][/tr]
[tr][td]
7​
[/td][td]Staff 3[/td][td]
300.00​
[/td][td]
150.00​
[/td][td]
300.00​
[/td][td]
400.00​
[/td][td]
=SUM($C7:F7)​
[/td][/tr]
[tr][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
=SUM($C8:F8)​
[/td][/tr]
[tr][td]
9​
[/td][td]Total[/td][td]
=SUM(C$5:C8)​
[/td][td]
=SUM(D$5:D8)​
[/td][td]
=SUM(E$5:E8)​
[/td][td]
=SUM(F$5:F8)​
[/td][td]
=SUM($C9:F9)​
[/td][/tr]
[tr][td]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td="bgcolor:#FFF3CB"]Expenses[/td][td="bgcolor:#FFF3CB"]
Apr 16​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(C11,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(D11,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(E11,1)​
[/td][td="bgcolor:#FFF3CB"]
Total​
[/td][/tr]
[tr][td]
12​
[/td][td]category 1[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
=SUM($C12:F12)​
[/td][/tr]
[tr][td]
13​
[/td][td]category 2[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
=SUM($C13:F13)​
[/td][/tr]
[tr][td]
14​
[/td][td]category 3[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
=SUM($C14:F14)​
[/td][/tr]
[tr][td]
15​
[/td][td]Total[/td][td]
=SUM(C$12:C14)​
[/td][td]
=SUM(D$12:D14)​
[/td][td]
=SUM(E$12:E14)​
[/td][td]
=SUM(F$12:F14)​
[/td][td]
=SUM($C15:F15)​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Budget[/td][/tr][/table]

For a column addition

Using Excel 2007
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][/tr][tr][td]
4​
[/td][td="bgcolor:#E2EFD9"]Salesperson[/td][td="bgcolor:#E2EFD9"]
Apr 16​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(C4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(D4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(E4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(F4,1)​
[/td][td="bgcolor:#E2EFD9"]
Total​
[/td][/tr]
[tr][td]
5​
[/td][td]Staff 1[/td][td]
500.00​
[/td][td]
200.00​
[/td][td]
100.00​
[/td][td]
700.00​
[/td][td][/td][td]
=SUM($C5:G5)​
[/td][/tr]
[tr][td]
6​
[/td][td]Staff 2[/td][td]
450.00​
[/td][td]
500.00​
[/td][td]
600.00​
[/td][td]
200.00​
[/td][td][/td][td]
=SUM($C6:G6)​
[/td][/tr]
[tr][td]
7​
[/td][td]Staff 3[/td][td]
300.00​
[/td][td]
150.00​
[/td][td]
300.00​
[/td][td]
400.00​
[/td][td][/td][td]
=SUM($C7:G7)​
[/td][/tr]
[tr][td]
8​
[/td][td]Total[/td][td]
=SUM(C$5:C7)​
[/td][td]
=SUM(D$5:D7)​
[/td][td]
=SUM(E$5:E7)​
[/td][td]
=SUM(F$5:F7)​
[/td][td]
=SUM(G$5:G7)​
[/td][td]
=SUM($C8:G8)​
[/td][/tr]
[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td="bgcolor:#FFF3CB"]Expenses[/td][td="bgcolor:#FFF3CB"]
Apr 16​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(C10,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(D10,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(E10,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(F10,1)​
[/td][td="bgcolor:#FFF3CB"]
Total​
[/td][/tr]
[tr][td]
11​
[/td][td]category 1[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td][/td][td]
=SUM($C11:G11)​
[/td][/tr]
[tr][td]
12​
[/td][td]category 2[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td][/td][td]
=SUM($C12:G12)​
[/td][/tr]
[tr][td]
13​
[/td][td]category 3[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td][/td][td]
=SUM($C13:G13)​
[/td][/tr]
[tr][td]
14​
[/td][td]Total[/td][td]
=SUM(C$11:C13)​
[/td][td]
=SUM(D$11:D13)​
[/td][td]
=SUM(E$11:E13)​
[/td][td]
=SUM(F$11:F13)​
[/td][td]
=SUM(G$11:G13)​
[/td][td]
=SUM($C14:G14)​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Budget[/td][/tr][/table]

And for both

Using Excel 2007
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][/tr][tr][td]
4​
[/td][td="bgcolor:#E2EFD9"]Salesperson[/td][td="bgcolor:#E2EFD9"]
Apr 16​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(C4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(D4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(E4,1)​
[/td][td="bgcolor:#E2EFD9"]
=EDATE(F4,1)​
[/td][td="bgcolor:#E2EFD9"]
Total​
[/td][/tr]
[tr][td]
5​
[/td][td]Staff 1[/td][td]
500.00​
[/td][td]
200.00​
[/td][td]
100.00​
[/td][td]
700.00​
[/td][td][/td][td]
=SUM($C5:G5)​
[/td][/tr]
[tr][td]
6​
[/td][td]Staff 2[/td][td]
450.00​
[/td][td]
500.00​
[/td][td]
600.00​
[/td][td]
200.00​
[/td][td][/td][td]
=SUM($C6:G6)​
[/td][/tr]
[tr][td]
7​
[/td][td]Staff 3[/td][td]
300.00​
[/td][td]
150.00​
[/td][td]
300.00​
[/td][td]
400.00​
[/td][td][/td][td]
=SUM($C7:G7)​
[/td][/tr]
[tr][td]
8​
[/td][td]Total[/td][td]
=SUM(C$5:C7)​
[/td][td]
=SUM(D$5:D7)​
[/td][td]
=SUM(E$5:E7)​
[/td][td]
=SUM(F$5:F7)​
[/td][td]
=SUM(G$5:G7)​
[/td][td]
=SUM($C8:G8)​
[/td][/tr]
[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td="bgcolor:#FFF3CB"]Expenses[/td][td="bgcolor:#FFF3CB"]
Apr 16​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(C10,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(D10,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(E10,1)​
[/td][td="bgcolor:#FFF3CB"]
=EDATE(F10,1)​
[/td][td="bgcolor:#FFF3CB"]
Total​
[/td][/tr]
[tr][td]
11​
[/td][td]category 1[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td]
100.00​
[/td][td][/td][td]
=SUM($C11:G11)​
[/td][/tr]
[tr][td]
12​
[/td][td]category 2[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td]
50.00​
[/td][td][/td][td]
=SUM($C12:G12)​
[/td][/tr]
[tr][td]
13​
[/td][td]category 3[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td]
20.00​
[/td][td][/td][td]
=SUM($C13:G13)​
[/td][/tr]
[tr][td]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
=SUM($C14:G14)​
[/td][/tr]
[tr][td]
15​
[/td][td]Total[/td][td]
=SUM(C$11:C14)​
[/td][td]
=SUM(D$11:D14)​
[/td][td]
=SUM(E$11:E14)​
[/td][td]
=SUM(F$11:F14)​
[/td][td]
=SUM(G$11:G14)​
[/td][td]
=SUM($C15:G15)​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Budget[/td][/tr][/table]
_........

Let me know if these changes to your initial Spreadsheet would be acceptable please ( and let me know that you agree the first set of screen shots in Post # 2 were in error as I have suggested ). Then i will drop the codes off as soon as i can..

Alan
 
Upvote 0
Re: VBA Inserting columns and keeping formulas in Table Ranges

DocAElstein said:
Let me know if these changes to your initial Spreadsheet would be acceptable please ( and let me know that you agree the first set of screen shots in Post # 2 were in error as I have suggested ). Then i will drop the codes off as soon as i can..

Alan
Click to expand...

Hi Alan, apologies for not picking that up. I agree with your changes.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Need to add column header values to this code
Replies
1
Views
52
Osvaldo Palmeiro
O
A
  • Question Question
How can I create a button to insert a new row and update related formulas?
Replies
0
Views
87
Arlochorim
A
RichTom44
  • Question Question
VBA/Excel Formula to insert rows
Replies
3
Views
352
Peter_SSs
Peter_SSs
R
  • Question Question
[VBA] - Need to Hide one column and need sum of columns
Replies
0
Views
182
rsolanki
R
M
  • Solved
Insert new row with incremental formula
Replies
8
Views
479
Sheik
S

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

Share this page

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
Back
Top