Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Button to add column to the end of a table

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

    Default Button to add column to the end of a table

    Hi,

    I would like to be able to assign a command button to add a new column to the end of my table. Each new column added should appear to the right of the last new column add. The command button will be on the activesheet but the table in which I am trying to add columns to will be on sheet “Competitor Overview Data”. My table range is from D4:S52 and the name of the range is “CompTable”.

    Any ideas?

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Button to add column to the end of a table

    Hi, you could try:

    Code:
    Sub addtablecolumn()
    With Sheets("Competitor Overview Data").ListObjects("CompTable")
        .ListColumns.Add (.ListColumns.Count + 1)
    End With
    End Sub
    Last edited by FormR; Aug 21st, 2019 at 11:07 AM.
    [code]your code[/code]

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

    Default Re: Button to add column to the end of a table

    That did not work. I tried to adjust it too by adding the CommandButton to the sub. This is what I tried:

    Code:
    Private Sub CommandButton2_Click()
    With Sheets("Competitor Overview Data").ListObjects("CompTable")
        .ListColumns.Add (.ListColumns.Count + 1)
    End With
    End Sub
    A message popped up saying "subscript out of range"

    Also, it doesn't matter if the new column comes off of "CompTable. I just need the columns to be add after column S because that's where the table ends right now. As long as the button adds new columns to the right of the last column starting on column S, I should be good.
    Last edited by gaudrco; Aug 21st, 2019 at 11:32 AM.

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,065
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Button to add column to the end of a table

    Subscript out of range normally means that something is named incorrectly. Double check that your sheet name and table name exactly match including capitalisation and for leading and trailing spaces.
    Last edited by MARK858; Aug 21st, 2019 at 04:52 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

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

    Default Re: Button to add column to the end of a table

    I typed everything correct and it still doesn’t work. How about a code for the command button to add a column to the right of column S on the “Competitor Overview Data” tab. With every button push putting a new column to the right of the last added column?

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,065
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Button to add column to the end of a table

    FormR's code works fine for me. First of all you describe it as a table then you state "the name of the range is “CompTable”".
    So is it an actual table (where you selected the range then clicked Insert then clicked table) or a range?

    If it is a table make sure that the Immediate window is open then run the code below. Copy and Paste the results in the thread (do not type the results in the thread, copy and paste it).

    Code:
    Sub ListTables()
        Dim xTable As ListObject, xSheet As Worksheet
     
        For Each xSheet In Worksheets
            For Each xTable In xSheet.ListObjects
                Debug.Print "| &"; xSheet.Name; "& |", Len(xSheet.Name) & " "; "| &"; xTable.Name; "& |", Len(xTable.Name)
            Next xTable
        Next
    End Sub
    How about a code for the command button to add a column to the right of column S

    Not until we sort out what your issue is.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

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

    Default Re: Button to add column to the end of a table

    It is not an actual table, it’s a defined range. And I’m not sure if this makes a difference but the headers of my defined range are not in row 1, they are in row 4.

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,065
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Button to add column to the end of a table

    It is not an actual table
    That is why the code by FormR doesn't work for you try


    Code:
    Sub ResizeNamedRange2()
        Dim xName As Name
        Set xName = ActiveWorkbook.Names.Item("CompTable")
        With xName
            .RefersTo = .RefersToRange.Resize(, Sheets("Competitor Overview Data").Range("CompTable").Columns.Count + 1)
        End With
    End Sub
    Last edited by MARK858; Aug 21st, 2019 at 06:47 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Button to add column to the end of a table

    Quote Originally Posted by gaudrco View Post
    It is not an actual table
    Maybe you could change it to an actual table?
    [code]your code[/code]

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

    Default Re: Button to add column to the end of a table

    I changed it to a table an it worked! The one thing is does not do is match the formatting of the entire column. Since my table headers start on row 4, everything above thenew columns is blank formatting. The new column only matches the formatting ofthe table range but I would like it to match everything that is in the column includingthe formatting above and below the table. Can this be adjusted?

    Also, just to note, I tried MARK858’s code to achieve thisbut I couldn’t seem to get it right.


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
  •