Add row and paste clipboard in Excel Table

[Delarc]

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:

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:

 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:

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:

 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

 

[Akuini]

@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:

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

 

[Delarc]

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]

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

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]

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

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

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):

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.

 

[Delarc]

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]

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

'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 ...

 

[Akuini]

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]

Thank you very much for your help...!