Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

Thread: Filling formulas over to the right into a new column added

  1. #1
    Board Regular
    Join Date
    Aug 2019
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filling formulas over to the right into a new column added

    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

  2. #2
    Board Regular
    Join Date
    Aug 2019
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    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.

  3. #3
    Board Regular
    Join Date
    Mar 2013
    Posts
    817
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    Here's a couple of links to sites dealing with tables that may assist you
    https://www.thespreadsheetguru.com/b...t-excel-tables
    https://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp

    Deal with just the table not entire column

  4. #4
    Board Regular
    Join Date
    Aug 2019
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    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.

  5. #5
    Board Regular
    Join Date
    Aug 2019
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

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

  6. #6
    Board Regular
    Join Date
    Mar 2013
    Posts
    817
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    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

  7. #7
    Board Regular
    Join Date
    Aug 2019
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    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

  8. #8
    Board Regular
    Join Date
    Mar 2013
    Posts
    817
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    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.

  9. #9
    Board Regular
    Join Date
    Aug 2019
    Posts
    193
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    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 by gaudrco; Aug 29th, 2019 at 03:53 PM.

  10. #10
    Board Regular
    Join Date
    Mar 2013
    Posts
    817
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •