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

 

[gaudrco]

I apologize for some of the spaces missing in my post. I don't know why that happens. I researched some options and it looks like autofill might be the best way to accomplish this.

 

[NoSparks]

Here's a couple of links to sites dealing with tables that may assist you
https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables
https://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp

Deal with just the table not entire column

 

[gaudrco]

Great sources thank you very much for sharing. It helped me understand exactly how to phase what I'm looking for. I would like the last column of my data body range to autofill the new column that is created when the button is pressed.

 

[gaudrco]

I tried to write this code but I can't get it to work. Can I have some help?

 

[NoSparks]

My Google searches somehow got me to this, which I had never heard of, then was a matter of establishing the last 2 listcolumns as the range.
Note that range.fillright fills both content and formatting.

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

 

[gaudrco]

Thank you for trying. When I ran this code and I got a run-time error 1004: Method 'Range' of object' Worksheet' failed.

I've been at this for hours

 

[NoSparks]

I generally interpret run-time error 1004 as Excel saying "Can't find what you're asking me to work with"
and it's usually because it doesn't exist, check that the sheet name or table name are correct.


Sheets("Competitor Comparison").ListObjects("CompComparisonTable") is what you had in the original post and what I used on my test sheet.

 

[gaudrco]

My names aredefinitely right. Could it be that for the line below, .ListColumns shouldactually be .ListObjects?

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

I don’t knowVBA very well so I'm probably wrong.

Edit:
I tried it and I got a run-time error '438' : Object doesn't support this property or method.

 

[NoSparks]

share a sanitized sample of your workbook that you're having this issue with and I'll take a look at what's happening.