Vba code - auto fill previous last column to the new last column

jamie.gill

New Member
Joined
Jan 28, 2011
Messages
30
I am trying to create a macro which adds a new column to a table. I have recorded the macro to add a column but i need the contents of the previous last column to autofill into the new last column - this is where i have difficulty.

Has anyone got any ideas which can help?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Why don't you just copy your last column ... to the next column ...killing two birds with the same stone ...:wink:

HTH
 

jamie.gill

New Member
Joined
Jan 28, 2011
Messages
30
Copying doesnt work.

The formula that is being copied is in a table titled "JAN_1" and references a table called "JAN":

=SUMIF(JAN[[AREA]:[AREA]],JAN_1[[AREA]:[AREA]],JAN[C4])

The final part (C4) needs to change to C5 (or whatever the name of the relative new column in JAN is). Copying always matches the the previous last column in JAN_1 exactly but auto filing allows the formula to change.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

If you are dealing with a Table ... you could test following :
Code:
[COLOR=#2D2C2A][FONT=Verdana]ActiveSheet.ListObjects("JAN").ListColumns.Add Position:=5[/FONT][/COLOR]

HTH
 

jamie.gill

New Member
Joined
Jan 28, 2011
Messages
30

ADVERTISEMENT

That adds a column in position 5 but doesn't do anything in respect of the autofil
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Always difficult to try to work out a solution ... without the actual worksheet ... :wink:

You could test the following :
Code:
ActiveSheet.ListObjects("JAN").ListColumns("Column name").DataBodyRange.FormulaR1C1 = "=SUMIF(JAN[[AREA]:[AREA]],JAN_1[[AREA]:[AREA]],JAN[C5])"

HTH
 

jamie.gill

New Member
Joined
Jan 28, 2011
Messages
30
Thanks for your help. I ended up using this - the selection at the beginning is the cell in the top row of the previous column.

Code:
Dim tbl As Integer
tbl = Range("JAN_1").Rows.Count - 1
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(0, 1)), Type:=xlFillDefault
    Selection.Offset(0, 1).Select
    Selection.Copy Destination:=Range(ActiveCell, ActiveCell.Offset(tbl, 0))
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Glad you could fix your problem ...

Thanks for sharing your solution with the Forum

:)
 

Forum statistics

Threads
1,137,155
Messages
5,679,921
Members
419,862
Latest member
Bluewings666

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