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 made a sample but I'm not sure how to send it to you or post it here.

 

[NoSparks]

Have used the free limited file storage and sharing at box.com in the past.

 

[gaudrco]

Sure but couldn’t I just email it to you?

 

[NoSparks]

Here's my test file.
https://app.box.com/s/a3mdnf0un8dctqby26ckfqte7kbj06r4

 

[gaudrco]

You can find my file here https://www.ozgrid.com/forum/forum/...om-a-table-to-a-new-column-added-to-the-table

 

[NoSparks]

how about

    Dim ws As Worksheet, oLo As ListObject

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

 

[Joe4]

You can find my file here https://www.ozgrid.com/forum/forum/h...d-to-the-table

We see that you have Cross-Posted the question across multiple forums. While we do not prohibit that here, we do ask that you mention that you are doing so, and provide links to all the other places where you may have posted the question.

A complete explanation of the rule can be seen here (rule 13): https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Thank you.

 

[gaudrco]

I tried it and still no luck. Everything worked up until the last line with the autofillright. I got a runtime error 424: Object required

And to Joe4: I apologize I didn’t know that rule but that is the only other place my question is posted

 

[Joe4]

And to Joe4: I apologize I didn’t know that rule but that is the only other place my question is posted

OK. Just keep that in mind, and be sure to do that if you want to Cross-Post again in the future.

 

[gaudrco]

I have still been at this and I had an idea. I used the Macro Recorder feature in Excel to create a base understanding of what the code should look like. Here is the recording:

Private Sub CommandButton1_Click()    
Sheets("Competitor Comparison").Select
    Range("CompComparisonTable[Competitor_5]").Select
    Selection.AutoFill Destination:=Range( _
        "CompComparisonTable[[Competitor_5]:[Competitor_6]]"), Type:=xlFillDefault
    Range("CompComparisonTable[[Competitor_5]:[Competitor_6]]").Select
    End Sub

I know this is probably incomplete because it was recorded but it definitely helped me. So the thing that needs to change in the code is:

Instead of [Competitor_5], that should be the second to last DataBodyRange column.
Instead of [Competitor_6), that should be the last DataBodyRange column.

Did this make it easier to understand?