New Group of Rows to a Table

default_name

Board Regular
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 a moderator:

sandy666

Well-known Member
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?
 

sandy666

Well-known Member
this is simply copy/paste and nothing to expand or I don't understand your logic
 

sandy666

Well-known Member
you can try PowerQuery (Get&TRansform)

Code:
[SIZE=1]// 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[/SIZE]
 

NoSparks

Well-known Member
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
 

default_name

Board Regular
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
    [COLOR=#ff0000]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[/COLOR]
    
    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
 

default_name

Board Regular
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

This Week's Hot Topics

Top