Copy table into part of other table between 2 workbooks

user_14936923 0
Viewed 32

What I Have: I have two workbooks, one with some data I need, and one which acts as a general template. When the code I run the template saves as a copy and should then fetch some data from the data workbook.

I want to copy the data of a table from the data workbook into a similarly structured, but with extra columns, table in the template workbook. It is worth noting that the table in the template workbook, is empty and only has 1 row

Currently, the code looks something like this.

Initial

Set chWb = Excel.Workbooks.Open(LinkToDataSource)
Set sht = chWb.Worksheets(SheetWithTable)
Set tbl = sht.ListObjects(TableWeWantToCopyFrom)
' Same set for the one we want to copy to
Set checkTbl = SheetName.ListObjects(TableWeWantToPasteInto)

Then the methods i have tried

'Selecting and copy the data
tbl.DataBodyRange.Select.Copy
'Paste
checkTbl.DataBodyRange.PasteSpecial xlPasteValues

This Copies, from what I can see, correctly from the datasheet. However, it throws an error 91 "Object variable or with block not set" on the line that in theory should paste it.

Maybe adding the row space first? Tried to add space first using the ListRow.Count of the data table

checkTbl.RowList.Add(tbl.ListRows.Count)

But this line in itself throws a Subscript out of range error

Any ideas and knowledge are welcome.

vba excel
1 Answers

So I managed to track down the error and with a helping hand from @Thomans C. found the solution

The first note is as Thomas says, "you can't paste into a DataBodyRange if it doesn't exist" Thus we should add Rows to the table before we can paste them into it.

The other note is understanding the Table.ListRows.Add function. The function has two arguments (Position, AlwaysInsert) and not the number of rows to add as an argument as I initially thought. Documentation

If the function is called without arguments the function adds a row at the bottom of the table. This means that we can simply call the function for every Row we need.

A simple for loop, using the Count from the data table

For c = 1 To tbl.ListRows.Count
   checkTbl.ListRows.Add
   Next
user_149369230
Related