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 I did:
via input box added competitor_16, it was X:X should have been W:W
Selected entire columns R, S & T together and deleted them
via input box added competitor_15 back in.
now adding competitor_16 it's W:W
and adding competitor_17 it's X:X

 

[gaudrco]

It is working for now. I have a feeling this is going to be one of those things that causes problems down the road. Thank you for this. You saved me a lot of stress.

 

[gaudrco]

As expected, the problem returned. Almost immediately actually but I didn't want to take up your time with my one issue because you have helped me immensely. I think I will start a new thread that addresses my issue specifically. You solved this thread of filling the formulas over to the right.

 

[gaudrco]

I think I solved it.

The way I solved this issue was by moving the line of code (that fills the column to the right) farther down in my code under the line that has a message box. By doing this, I believe it gave Excel more time to process the adjustment of adding a column. Before, everything was happening so quickly that I don't think excel could process the new column in time to fill the right formula into it.

 

[gaudrco]

how about

    Dim ws As Worksheet, oLo As ListObject

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

How would you alter this so that the table selection range is the whole databodyrange minus the first column?

 

[NoSparks]

With oLo
    Set rng = .DataBodyRange.Offset(, 1).Resize(, .ListColumns.Count - 1)
End With

 

[gaudrco]

This did what I was looking for thank you