使用 Excel VBA 将行从一个列表对象复制到另一个列表对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20097234/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying rows from one listobject to another using Excel VBA
提问by AMPorter
I want to create a peice of VBA code which will select the first 10 rows from a ListObject and then paste them into another ListObject. Once a collection of rows has been copied to the new sheet, they will be processed, then the next 10 rows will be selected and processed, and so on.
我想创建一段 VBA 代码，它将从 ListObject 中选择前 10 行，然后将它们粘贴到另一个 ListObject 中。将一组行复制到新工作表后，将对其进行处理，然后将选择并处理接下来的 10 行，依此类推。
I can achieve this using standard VBA functions, but I would like to achieve this using the ListObject, i.e.
我可以使用标准的 VBA 函数来实现这一点，但我想使用 ListObject 来实现这一点，即
Sub PopulateSectionOfData()
Dim loInput As ListObject
Dim loOutput As ListObject
Dim intNumOfColumns As Integer
Dim rngToCopy As Range
Dim intRowsInFile As Integer
' Create the inout and output ListObjects
Set loInput = Worksheets(WS_INPUT).ListObjects(LO_INPUT)
Set loOutput = Worksheets(WS_OUTPUT).ListObjects(LO_OUTPUT)
' Delete all the current rows from the output table
Worksheets(WS_OUTPUT).ListObjects(LO_OUTPUT).DataBodyRange.Delete
' Set variables
intNumOfColumns = loInput.ListColumns.Count
WorkbookCounter = 1
intRowsInFile = 10 ' Select 10 records at a time
' Get 10 records at a time from the input file
For i = 1 To loInput.DataBodyRange.Rows.Count Step intRowsInFile - 1
'???? - This is the area that i am unsure on
Set rngToCopy = loInput.DataBodyRange(p, 1)
rngToCopy.Copy loOutput
'????
' TODO - Add further processing here
Next i
' Clear used objects
Set loInput = Nothing
Set loOutput = Nothing
End Sub
Any help on this matter would be most appreciated as I would like to use ListObjects
对此事的任何帮助将不胜感激，因为我想使用 ListObjects
Thank you in advance
先感谢您
回答by Jaycal
DataBodyRangerefers to the actual range of rows and columns in the table. If there are no rows in the table (which is your condition after executing DataBodyRange.Delete, you'll need to add a row to the table first; then, you can copy and paste data into that row, with Excel expanding the table accordingly
DataBodyRange指表中行和列的实际范围。如果表中没有行（这是执行后的条件DataBodyRange.Delete，则需要先向表中添加一行；然后，您可以将数据复制并粘贴到该行中，Excel 会相应地扩展表
Your forloop can be updated to read as follows:
您的for循环可以更新为如下所示：
For i = 1 To loInput.DataBodyRange.Rows.Count Step intRowsInFile - 1
' First add a blank row to the table
loOutput.ListRows.Add
' Check that 10 rows are available in the input table
' (Done to avoid adding blank rows to the output table)
If i + 9 > loInput.DataBodyRange.Rows.Count Then
loInput.DataBodyRange.Rows(i & ":" & loInput.DataBodyRange.Rows.Count) _
.Copy loOutput.DataBodyRange.Rows(i)
Else
loInput.DataBodyRange.Rows(i & ":" & i + 9).Copy loOutput
End If
Next i
回答by Lucius Matos
Remember that "DataBodyRange" returns a Range. So do this:
请记住，“DataBodyRange”返回一个范围。所以这样做：
'copies entire line 1 from "loInput" to "loOutput" on line 2
loOutput.DataBodyRange.Rows(2) = loInput.DataBodyRange.Rows(1)
In addition, just make a FOR to process the rest
另外，做一个 FOR 来处理剩下的
