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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 :(
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
share a sanitized sample of your workbook that you're having this issue with and I'll take a look at what's happening.
 
Upvote 0

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top