Filling formulas over to the right into a new column added

gaudrco

Board Regular
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:

Code:
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

Board Regular
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.
 

gaudrco

Board Regular
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

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

NoSparks

Well-known Member
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.
Code:
    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

Board Regular
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

Well-known Member
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

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

Code:
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.
 
Last edited:

NoSparks

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

Some videos you may like

This Week's Hot Topics

Top