Excel将数据从列表对象A(tableA)的几列复制到列表对象B(tableB)的一列中 [英] Excel copy data from several columns of listobject A (tableA) into one column of listobject B (tableB) one after the other

问题描述

我有一个工作簿，其中包含几张工作表，每张工作表都包含一个列表对象(一个表)

I have a workbook containing several sheets each one including a listobject (a table)

我的目的是从不同的列表对象中获取数据并将其粘贴到另一个列表对象中.或者更准确地说，是在另一个表中一个接一个地复制几个表的几列.

My intention is to get data from different list objects and paste it in another listobject. Or more precisely copying several columns of several tables one after the other in other table.

到目前为止，我正在尝试:

So far I was trying this:

'defining the names of the list objects
Dim OriginTable As ListObject
Set OriginTable = ThisWorkbook.Sheets("claims").ListObjects(1)
Dim destinationTable  As ListObject
Set destinationTable = ThisWorkbook.Sheets("COMM").ListObjects(1)

'inserting one row in case the table is empty.
If destinationTabl.ListColumns(1).DataBodyRange Is Nothing Then
    destinationTabl.ListRows.Add
End If

lastItem = destinationTable.ListColumns(1).DataBodyRange.Count+1
MsgBox ("I am going to insert in: ", lastItem)
originTable.ListColumns("comm").DataBodyRange.Copy Destination:=destinationTable.listcolumns(1).databodyrange.item(lastitem)

这不起作用. 原因是destinationTable.listcolumns(1).databodyrange.item(lastitem)不是列最后一个单元格的范围，而是其他内容.

This does not work. Reason being that destinationTable.listcolumns(1).databodyrange.item(lastitem) is not THE range of the last cell of the column, but something else.

我正在玩range()、. address属性等，无济于事地阅读了一些其他stackoverflow问题.

I was playing around with range(), .address property etc, read some other stackoverflow questions at no avail.

有人可以帮忙吗? 简而言之，获取范围的日期并将其从另一个列表对象的最后一个列单元格中粘贴

Could someone help? IN short again, getting the date of a range and paste it from the last column cell in another listobject

我试图避免使用.select，因为选择总是有问题的.

I am trying to avoid using .select since selecting is always problematic.

推荐答案

感谢@QHarr，我得到了答案:

Thanks to @QHarr I got the answer:

With COMMtempTbl.ListColumns(1).DataBodyRange
originTable.ListColumns("comm").DataBodyRange.Copy Destination:=sheets("destination").range(.Cells(.Rows.Count + 1, 1).address)
End With

说明. 原始副本范围很清楚.

Explanation. the origin copy range is clear.

通过获取目标表第1列的最后一个单元格的地址来完成目标范围.但是，您必须将其引用到相应的工作表中，因为地址的范围将引用到活动工作表中，而不必引用目标工作表.

the destination range is done by getting the address of the last cell of the column 1 of the destination table. BUT you have to refer it to the corresponing sheet, since the range of an addres would be refered to the active sheet, and not necessary the destination sheet.

在(.Rows.Count + 1，1)中，可以用相应的列号替换第二个1.

In (.Rows.Count + 1, 1) it would be possible to substitute the second 1 by the corresponding column number.

这篇关于Excel将数据从列表对象A(tableA)的几列复制到列表对象B(tableB)的一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！