Filling formulas over to the right into a new column added

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
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
Joined
Aug 16, 2019
Messages
203
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
Joined
Aug 16, 2019
Messages
203
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
Joined
Aug 16, 2019
Messages
203
I tried to write this code but I can't get it to work. Can I have some help?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
853
Office Version
2010
Platform
Windows
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
Joined
Aug 16, 2019
Messages
203
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
Joined
Mar 15, 2013
Messages
853
Office Version
2010
Platform
Windows
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
Joined
Aug 16, 2019
Messages
203
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
Joined
Mar 15, 2013
Messages
853
Office Version
2010
Platform
Windows
share a sanitized sample of your workbook that you're having this issue with and I'll take a look at what's happening.
 

Forum statistics

Threads
1,085,802
Messages
5,385,980
Members
401,978
Latest member
BrianThompsonTenTen

Some videos you may like

This Week's Hot Topics

Top