CODEWITHSUNDEEP
excelvba
Edgar
Edgar

Reputation: 503

How to writing values from 2 separate data tables across two worksheets in Excel using VBA

I have an excel sheet that has several predefined data tables. I Need to copy specific column values and insert into another predefined table on a different worksheet. if I can get this to work on one I can get the rest. Ive seen similar posts but they are not moving from a predefined table to another (from ribbon: Insert>Table). the below VBA Code in the module throws "Object doesnt support this property or method." im assuming the ListObject Object cant call a cell the way this is calling it. not sure how to insert that data into that row.

  Sub Search_and_Copy_Invoices()
    '  Application.SendKeys "^g ^a {DEL}"

     Dim tbl As ListObject
     Dim tblDest As ListObject
     Dim lrow As Range
     Dim RowNum As Long

     RowNum = 0

    Set tbl = Sheet3.ListObjects("tbl_Invoices")
    Set tblDest = Sheet4.ListObjects("tbl_Quarter1")

    For Each lrow In tbl.ListColumns("Invoice#").DataBodyRange.Rows
       ' Debug.Print "Invoice" & vbTab & lrow.Offset(0, 2) & vbTab & lrow.Offset(0, 5)
       tblDest.Cells(RowNum, 1).Value = "Invoice"
       tblDest.Cells(RowNum, 2).Value = lrow.Offset(0, 2)
       tblDest.Cells(RowNum, 3).Value = lrow.Offset(0, 5)
     
       RowNum = RowNum + 1
    Next lrow
    RowNum = 0
 End Sub

Another potential issue is inserting the other datatable data into that same table, i dont want to replace the data just append to it. and i need to clear that table every time the method calling all these subroutines is executed.

Upvotes: 0

Views: 146

Answers (1)

Andras
Andras

Reputation: 403

I think these lines in your For Each loop starting with:

tblDest.Cells(RowNum, 1).Value ...

Should rather be like this:

tblDest.DataBodyRange.Cells(RowNum, 1).Value ...

Upvotes: 1

Related Questions