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

[jamie.gill]

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?

 

[James006]

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]

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]

Hello,

If you are dealing with a Table ... you could test following :

[COLOR=#2D2C2A][FONT=Verdana]ActiveSheet.ListObjects("JAN").ListColumns.Add Position:=5[/FONT][/COLOR]

HTH

 

[jamie.gill]

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

 

[James006]

Always difficult to try to work out a solution ... without the actual worksheet ... :wink:

You could test the following :

ActiveSheet.ListObjects("JAN").ListColumns("Column name").DataBodyRange.FormulaR1C1 = "=SUMIF(JAN[[AREA]:[AREA]],JAN_1[[AREA]:[AREA]],JAN[C5])"

HTH

 

[jamie.gill]

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.

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]

Glad you could fix your problem ...

Thanks for sharing your solution with the Forum