Add row and paste clipboard in Excel Table

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I would like to take advantage of the Excel Tables.
But I can't solve this task:

I copy certain content from my Active Sheet to the clipboard:

VBA Code:
Range("F3:F19").Copy

Now I intend to paste that data into a new row of a Table that is on another Sheet.
To access the table and add a new row to it, I use:

VBA Code:
 Range("TableName").ListObject.ListRows.Add

So far it works fine.
Now the problem is how to Paste the content of the clipboard in that created row.
The idea is to use something like:

VBA Code:
PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=True

But I found that to use PasteSpecial I have to resign the advantages of the Table and treat it as a "common range", using a code like this:

VBA Code:
 Sheets("NameSheetWithTable").Select
    Range("B3").Select 'would be the upper left cell of the Table
    Selection.End(xlDown).Select
    If ActiveCell.Value <> "" Then ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=True
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
@Delarc Welcome to the Forum.
You don't need to add a new row to the table. If you paste data right below the last row of a table then it will become the new rows (part of the table).
You can do something like this:

VBA Code:
Sub a1157743a()
Range("F3:F19").Copy
     With Sheets("Sheet1").ListObjects("Table1").Range
    
        .Offset(.Rows.Count)(1).PasteSpecial xlPasteValues
   
     End With
End Sub
 

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hello Akuini, thank you very much... I love your code for how short and concise.

The problem I find is that if the table is empty, that is: it only has headers and an empty first row of data, the data is pasted in the first row below the Table, and it is not integrated into it.
If the table has at least one row with some data, then the code works like a charm.

But what I am trying to do is refer to the Table WITHOUT HAVING TO PRIOR INDICATE THE SHEET.

Range ("TableName"). ListObject.ListRows.Add

That line of code adds a row to the table without first having to indicate the SHEET. That works...

Perhaps there is some detail of VBA that I am not understanding well.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
But what I am trying to do is refer to the Table WITHOUT HAVING TO PRIOR INDICATE THE SHEET.

You can do that.
When you create an actual/named table, Excel will create a named range for that table (excluding the header).

I amended the code to deal with the situation where the table is empty.
Note:
1. I assume there is no data below the table, otherwise we'll need another code.
2. Change "Table1" to suit
VBA Code:
Sub a1157743c()

Range("F3:F19").Copy

With Range("Table1")

    n = Columns(.Column).Resize(, .Columns.Count).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Cells(n + 1, .Column).PasteSpecial xlPasteValues
End With

End Sub
 

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It seems that we are getting closer to the solution ...!

Sheet1 is Active and from there I copy the data with:

VBA Code:
Range ("F3: F19"). Copy

On Sheet2 is my empty "TestTable" with 17 columns from D29 (headings in row D28)
When running your code (I added Transpose: = True):

VBA Code:
With Range ("TestTable")
n = Columns (.Column) .Resize (, .Columns.Count) .Find ("*", SearchOrder: = xlByRows, SearchDirection: = xlPrevious) .Row
Cells (n + 1, .Column) .PasteSpecial xlPasteValues, Transpose: = True
End With

Result: The data was pasted into Sheet1, Row 46, from column D

In other words, the column is correct ... the row and the sheet failed.
 
Last edited by a moderator:

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Notice that "F3: F19" contains 17 data and the table is located in row 29 ... 17 + 29 = 46 which is the row number from which you paste the data
 

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello, inspired by your idea, I found this solution:

VBA Code:
'With this line of code, I copy data from ActiveSheet to the clipboard

Range ("f3: f19"). Copy

'I paste that data in the TestTable that it IS NOT in the ActiveSheet, with these lines:

With Range ("TestTable")
    .Cells (.Rows.Count, 1) .PasteSpecial xlPasteValues, Transpose: = True
    .ListObject.ListRows.Add
End With

It seems to work, but I would like it to be evaluated by more experienced people ...
 
Last edited by a moderator:
Solution

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
I didn't realize that you're talking about 2 different sheets.
I think your new code should work as long as you are sure that the last row of the table is empty, because the code paste the data on the last row of the table not the row below it.
 

Delarc

New Member
Joined
Jan 6, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Thank you very much for your help...!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top