Filling formulas over to the right into a new column added

[gaudrco]

I have a code for a button that creates a new column at theend of a table. After the new column is added, I would like to have theformulas from the previous table column to fill into the new column. The datain the table should be the only formulas filling to the right (table headersshould not fill, just the data from the table). Here is the code for adding the newcolumn:

With Sheets("Competitor Comparison").ListObjects("CompComparisonTable")
    .ListColumns.Add(.ListColumns.Count + 1).Name = Range("C2")
    .ListColumns(.ListColumns.Count - 1).Range.EntireColumn.Copy
    .ListColumns(.ListColumns.Count).Range.EntireColumn.PasteSpecial Paste:=xlPasteFormats
End With

Please and thank you

 

[NoSparks]

What's wrong with the code in post 16 ?

 

[gaudrco]

Hmm.. Very odd; that code is working for me now. I wonder why I couldn't get it to work the first time I tried. I apologize for dragging this thread out for longer than it needed to be. I really appreciate your help

Thanks!

 

[gaudrco]

Scratch that.

The fill command is not behaving correctly. When I click the button to add a new column, the new column does not fill the correct column starting point.

Here is the reference column formula:
=IFERROR(INDEX('Competitor Comparison Data'!U:U,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")

Here is the new column formula that is filled into the new column when the button is clicked:
=IFERROR(INDEX('Competitor Comparison Data'!W:W,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")

So it skipped over V:V and went to W:W. Additionally, when I add a second column, the first added column changes from W:W to X:X.

Once I add a column, formulas in existing columns should not change.

So two issues.
1. The first column added is not starting with the correct reference column; its skipping over V:V
2. Every new column added changes the reference column of all the other added columns. If I added 3 columns, the order of my last 4 columns would be as follows:

=IFERROR(INDEX('Competitor Comparison Data'!U:U,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match") Correct Column
=IFERROR(INDEX('Competitor Comparison Data'!Y:Y,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")
=IFERROR(INDEX('Competitor Comparison Data'!Z:Z,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")
=IFERROR(INDEX('Competitor Comparison Data'!AA:AA,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")

 

[gaudrco]

how about

    Dim ws As Worksheet, oLo As ListObject

Set ws = Sheets("Competitor Comparison")
With ws
    Set oLo = .ListObjects("CompComparisonTable")
    With oLo
        .ListColumns.Add
        oLo.HeaderRowRange.Cells(1, .ListColumns.Count).Value = Range("C2")
        ws.Range(.ListColumns(.ListColumns.Count - 1).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight
    End With
End With

I changed

ws.Range(.ListColumns(.ListColumns.Count - 1).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight

To

ws.Range(.ListColumns(.ListColumns.Count - 2).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight

That seems to have fixed the problem

 

[NoSparks]

It's not the VBA.
Has to do with deleting columns, which you are likely doing lots in the testing process, and Excel automatically adjusting your formulas to account for it.
The file I posted over at that other forum had the same issue and I would have told you about it had you ever acknowledge my post there.

 

[gaudrco]

I'm sorry for not replying to your post on the other forum. You have been extraordinary with all your help and I can't thank you enough. Much appreciated.

Yeah changing the vba did not fix it. I thought it did because it was working. The problem occurs randomly. So when would this work normally? How long after deleting columns would the macro work? Is there a fix?

Thanks again

 

[NoSparks]

Delete a few entire columns from somewhere to the right of the table back to the last column with correct formulas.

 

[NoSparks]

From the linked page in post 6
Fills right from the leftmost cell or cells in the specified range. The contents and formatting of the cell or cells in the leftmost column of a range are copied into the rest of the columns in the range.
Your change in post 24 didn't go back far enough in the table for the start of the range.

 

[gaudrco]

Your change in post 24 didn't go back far enough in the table for the start of the range.

Okay so I deleted entire columns from the end of my table all the way to the end of excels available columns and that did not work. It still skipped over the column in the formula when I added a new column.
Then I tried to adjust the vba to go back far enough in my table for the start of the range, which equals ( - 17), so the new line of code was:

[COLOR=#333333]ws.Range(.ListColumns(.ListColumns.Count - 17).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight[/COLOR]

This did not fix it either. The last column in the formula I have is V:V and the new added column still goes to X:X.

Did I miss something?

 

[NoSparks]

Not guessing at anything any more.
Your choice whether to share the workbook or not.
See post 12.

if you don't want others to have the workbook, pm the link