Results 1 to 10 of 10

Thread: New Group of Rows to a Table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2018
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default New Group of Rows to a Table

    Hello,

    I have a table inside my excel document.
    The table is pretty large, and has bounds/data range of ($J$1:$BN$3073).

    I am trying to record/write a macro that will
    1. Find the end/bottom of this table
    2. Copy the 12 rows that are at the bottom of the table
    3. Paste/insert 12 new rows at the bottom (while expanding/increasing the table bounds/data range by 12) of the table

    I hope that makes sense.
    Thanks in advance for your help!

    EDIT:
    If it helps any, the name of the table is WChart_Data
    Last edited by Fluff; Aug 22nd, 2019 at 03:25 PM. Reason: Updated op

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

    Default Re: New Group of Rows to a Table

    By referring to a sheet range to tell us about the table it doesn't sound like you're using your table as a table.

    A couple of this forums members have sites that give insight into dealing with tables.
    https://www.thespreadsheetguru.com/b...t-excel-tables
    https://www.jkp-ads.com/Articles/Exc...lComments=True

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,858
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: New Group of Rows to a Table

    Quote Originally Posted by default_name View Post
    2. Copy the 12 rows that are at the bottom of the table
    3. Paste/insert 12 new rows at the bottom (while expanding/increasing the table bounds/data range by 12) of the table[/I]
    it seems like you want duplicate 12 last rows, am I right?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  4. #4
    Board Regular
    Join Date
    May 2018
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: New Group of Rows to a Table

    Quote Originally Posted by sandy666 View Post
    it seems like you want duplicate 12 last rows, am I right?
    Yes, I want to duplicate the last 12 rows as well as expand the table bounds when I do so.

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,858
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: New Group of Rows to a Table

    this is simply copy/paste and nothing to expand or I don't understand your logic
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,858
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: New Group of Rows to a Table

    you can try PowerQuery (Get&TRansform)

    Code:
    // WChart_Data
    let
        Source1 = Excel.CurrentWorkbook(){[Name="WChart_Data"]}[Content],
        Source2 = Excel.CurrentWorkbook(){[Name="WChart_Data"]}[Content],
        Last12 = Table.LastN(Source2, 12),
        TC = Table.Combine({Source1, Last12})
    in
        TC
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

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

    Default Re: New Group of Rows to a Table

    how about
    Code:
    Dim oLo As ListObject
    Set oLo = ActiveSheet.ListObjects("WChart_Data")
    With oLo
        .ListRows(.ListRows.Count - 11).Range.Resize(12).Copy .ListRows(.ListRows.Count).Range.Cells(1).Offset(1)
    End With

  8. #8
    Board Regular
    Join Date
    May 2018
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: New Group of Rows to a Table

    All of your input helped give me ideas on how to proceed.
    I was able to write a script that works the way I want it to work...however, the Selection.ListObject.ListRows.Add AlwaysInsert=True statement is in there 12 times, and takes a few extra seconds for Excel to step through.
    Is there a quicker way to do this, instead of listing the command 12 times?

    Thanks in advance!

    Code:
    Sub Add_New_Rows()
    '
    ' Add_New_Rows Macro
    '
    
    '
        Sheets("Sheet1").Select
        
        Range("CD2:CJ13").Select
        Selection.Copy
        Range("C2").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        Selection.Insert Shift:=xlDown
        
        Range("WChart_Data").Select
        Selection.End(xlDown).Select
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        Selection.ListObject.ListRows.Add AlwaysInsert:=True
        
        Range("CK2:FC13").Select
        Selection.Copy
        Range("WChart_Data").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
            
    End Sub

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

    Default Re: New Group of Rows to a Table

    Something wrong with post 7 ?

  10. #10
    Board Regular
    Join Date
    May 2018
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: New Group of Rows to a Table

    Quote Originally Posted by NoSparks View Post
    Something wrong with post 7 ?
    It would create the new rows in the table (correctly adjusting the size of the table as well).
    But it wasn't pasting the cells the way I wanted it. Your ListObject hint helped me find/put something together that works.

    Now I am just trying to make it more intuitive (instead of listing the function 12 times, I am stuck trying to make it carry out the function 12 times with one command).

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
  •