Help - VBA code inserting columns and keeping formulas

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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.

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
Row\Col
B
C
D
E
F
G
4
Salesperson
Apr 16
=EDATE(C4,1)
=EDATE(D4,1)
=EDATE(E4,1)
Total
5
Staff 1
500.00​
200.00​
100.00​
700.00​
=SUM(C5:F5)
6
Staff 2
450.00​
500.00​
600.00​
200.00​
=SUM(C6:F6)
7
Staff 3
300.00​
150.00​
300.00​
400.00​
=SUM(C7:F7)
8
Total
=SUM(C5:C7)
=SUM(D5:D7)
=SUM(E5:E7)
=SUM(F5:F7)
=SUM(C8:F8)
9
10
Expenses
Apr 16
=EDATE(C10,1)
=EDATE(D10,1)
=EDATE(E10,1)
Total
11
category 1
100.00​
100.00​
100.00​
100.00​
=SUM(C11:F11)
12
category 2
50.00​
50.00​
50.00​
50.00​
=SUM(C12:F12)
13
category 3
20.00​
20.00​
20.00​
20.00​
=SUM(C13:F13)
14
Total
=SUM(C11:C13)
=SUM(D11:D13)
=SUM(E11:E13)
=SUM(F11:F13)
=SUM(C14:F14)
Sheet1
_......

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

Using Excel 2007
Row\Col
B
C
D
E
F
G
H
4
Salesperson
Apr 16
=EDATE(C4,1)
=EDATE(D4,1)
=EDATE(E4,1)
=EDATE(F4,1)
Total
5
Staff 1
500.00​
200.00​
100.00​
700.00​
=SUM(D5:G5)
6
Staff 2
450.00​
500.00​
600.00​
200.00​
=SUM(D6:G6)
7
Staff 3
300.00​
150.00​
300.00​
400.00​
=SUM(D7:G7)
8
Total
=SUM(C5:C7)
=SUM(D5:D7)
=SUM(E5:E7)
=SUM(F5:F7)
=SUM(G5:G7)
=SUM(D8:G8)
9
10
Expenses
Apr 16
=EDATE(C10,1)
=EDATE(D10,1)
=EDATE(E10,1)
=EDATE(F10,1)
Total
11
category 1
100.00​
100.00​
100.00​
100.00​
=SUM(D11:G11)
12
category 2
50.00​
50.00​
50.00​
50.00​
=SUM(D12:G12)
13
category 3
20.00​
20.00​
20.00​
20.00​
=SUM(D13:G13)
14
Total
=SUM(C11:C13)
=SUM(D11:D13)
=SUM(E11:E13)
=SUM(F11:F13)
=SUM(G11:G13)
=SUM(D14:G14)
Sheet1
_..................................................................

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

Using Excel 2007
Row\Col
B
C
D
E
F
G
4
Salesperson
Apr 16
=EDATE(C4,1)
=EDATE(D4,1)
=EDATE(E4,1)
Total
5
Staff 1
500.00​
200.00​
100.00​
700.00​
=SUM(D5:F5)
6
Staff 2
450.00​
500.00​
600.00​
200.00​
=SUM(D6:F6)
7
Staff 3
300.00​
150.00​
300.00​
400.00​
=SUM(D7:F7)
8
=SUM(D8:F8)
9
Total
=SUM(C6:C8)
=SUM(D6:D8)
=SUM(E6:E8)
=SUM(F6:F8)
=SUM(D9:F9)
10
11
Expenses
Apr 16
=EDATE(C11,1)
=EDATE(D11,1)
=EDATE(E11,1)
Total
12
category 1
100.00​
100.00​
100.00​
100.00​
=SUM(D12:F12)
13
category 2
50.00​
50.00​
50.00​
50.00​
=SUM(D13:F13)
14
category 3
20.00​
20.00​
20.00​
20.00​
=SUM(D14:F14)
15
Total
=SUM(C12:C14)
=SUM(D12:D14)
=SUM(E12:E14)
=SUM(F12:F14)
=SUM(D15:F15)
Sheet1
-....

And so for Both.....

Using Excel 2007
Row\Col
B
C
D
E
F
G
H
4
Salesperson
Apr 16
=EDATE(C4,1)
=EDATE(D4,1)
=EDATE(E4,1)
=EDATE(F4,1)
Total
5
Staff 1
500.00​
200.00​
100.00​
700.00​
=SUM(D5:G5)
6
Staff 2
450.00​
500.00​
600.00​
200.00​
=SUM(D6:G6)
7
Staff 3
300.00​
150.00​
300.00​
400.00​
=SUM(D7:G7)
8
=SUM(D8:G8)
9
Total
=SUM(C6:C8)
=SUM(D6:D8)
=SUM(E6:E8)
=SUM(F6:F8)
=SUM(G6:G8)
=SUM(D9:G9)
10
11
Expenses
Apr 16
=EDATE(C11,1)
=EDATE(D11,1)
=EDATE(E11,1)
=EDATE(F11,1)
Total
12
category 1
100.00​
100.00​
100.00​
100.00​
=SUM(D12:G12)
13
category 2
50.00​
50.00​
50.00​
50.00​
=SUM(D13:G13)
14
category 3
20.00​
20.00​
20.00​
20.00​
=SUM(D14:G14)
15
Total
=SUM(C12:C14)
=SUM(D12:D14)
=SUM(E12:E14)
=SUM(F12:F14)
=SUM(G12:G14)
=SUM(D15:G15)
Sheet1

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

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

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

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..

.......
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.....
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

Do you always add a column in both tables, or should that also just be done on the selected table, sales or expense ?

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....:ROFLMAO: :eek:

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
Row\Col
B​
C​
D​
E​
F​
G​
4​
Salesperson
Apr 16
=EDATE(C4,1)
=EDATE(D4,1)
=EDATE(E4,1)
Total
5​
Staff 1
500.00​
200.00​
100.00​
700.00​
=SUM($C5:F5)
6​
Staff 2
450.00​
500.00​
600.00​
200.00​
=SUM($C6:F6)
7​
Staff 3
300.00​
150.00​
300.00​
400.00​
=SUM($C7:F7)
8​
Total
=SUM(C$5:C7)
=SUM(D$5:D7)
=SUM(E$5:E7)
=SUM(F$5:F7)
=SUM($C8:F8)
9​
10​
Expenses
Apr 16
=EDATE(C10,1)
=EDATE(D10,1)
=EDATE(E10,1)
Total
11​
category 1
100.00​
100.00​
100.00​
100.00​
=SUM($C11:F11)
12​
category 2
50.00​
50.00​
50.00​
50.00​
=SUM($C12:F12)
13​
category 3
20.00​
20.00​
20.00​
20.00​
=SUM($C13:F13)
14​
Total
=SUM(C$11:C13)
=SUM(D$11:D13)
=SUM(E$11:E13)
=SUM(F$11:F13)
=SUM($C14:F14)
Budget


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

For a row addition

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

For a column addition

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

And for both

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

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

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

Hi Alan, apologies for not picking that up. I agree with your changes.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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