Copy listobject table from one worksheet to another workbook

[Scoti]

Hello All,
I am working on a project which copies data from the current workbook into a "daily" workbook(similar to a tally sheet). The code successfully finds the most recent file and opens it however it won't copy the data. There are 4 sheets in the current workbook and a table on each sheet which needs to be copied into the first empty cell in column A. I am getting a "Object doesn’t support this property or method" error on the lo.Copy line. I'm not sure what I need to do to fix the issue. I don't necessarily need the tables to remain as tables. I do need them to paste as values only onto the daily sheet. Any advice?

Const sPath As String = "C:\Users\azwag\OneDrive\Desktop\testfolder\Data Hub\"
Const dtEarliest = #8/1/2023#


Sub dataHubUpload()


    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim i As Long
    Dim dt As Date
    Dim flName As String, dtPart As String
    Dim shtA As Worksheet
    Dim shtQ As Worksheet
    Dim shtT As Worksheet
    Dim shtP As Worksheet
    Dim shtA2 As Worksheet
    Dim shtQ2 As Worksheet
    Dim shtT2 As Worksheet
    Dim shtP2 As Worksheet
    Dim lo As ListObject
    Dim lo2 As ListObject
    Dim lo3 As ListObject
    Dim lo4 As ListObject
    Dim lDesLastRow As Long
    Dim rList As Range
    


    Set wb1 = ThisWorkbook
    Set shtA = wb1.Sheets("ASCU")
    Set shtQ = wb1.Sheets("QLT")
    Set shtT = wb1.Sheets("TCU")
    Set shtP = wb1.Sheets("Pb")
    
    dt = Format(Now(), "mm-dd-yy")


    Set lo = shtA.ListObjects("Table1")
    Set lo2 = shtQ.ListObjects("Table2")
    Set lo3 = shtT.ListObjects("Table3")
    Set lo4 = shtP.ListObjects("Table4")




    
If shtA.Range("D2") = "Morenci" Then


    '~~> Loop through dates in reverse
    For i = dt To dtEarliest Step -1
        dtPart = Format(i, "mm-dd-yyyy")
        '~~> Create your file name
        flName = "Morenci\Processed-" & Format$(i, "mm-dd-yyyy") & ".xlsx"


        '~~> Check if exists
        If Dir(sPath & flName) <> "" Then
            Set wb2 = Workbooks.Open(sPath & flName)
            Exit For
        End If
    Next i


    Set shtA2 = wb2.Sheets("ASCU")
    Set shtQ2 = wb2.Sheets("QLT")
    Set shtT2 = wb2.Sheets("TCU")
    Set shtP2 = wb2.Sheets("Pb")
  wb1.Activate
                lo.Copy
                wb2.shtA2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
                lo2.Copy
                wb2.shtQ2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
                lo3.Copy
                wb2.shtT2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
                lo4.Copy
                wb2.shtP2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
      
        Else
      
End If


End Sub

 

[Domenic]

Try replacing...

lo.copy

with

lo.Range.Copy

And the same thing for your other ones.

Hope this helps!

 

[Alex Blakenburg]

Just an additional suggestion:
is to leave wb2 active while you do the copy and move the wb1.Activate to after the copy.
I guess the chances are small but if:
• wb1 is active
• and the sheet with the tables is the active sheet
• and the active cell is inside one of the tables
• and that table has a filter on it.
The copy will only copy the filtered list (visible cells)

I did say the chances were small right