VBA Tables.

[RandomPezzer]

Hi guys,

Can you someone help me, please. I need to be able to copy data from cells B8:D8 to a table in another workbook, by finding the lastrow of the table and then pasting the data.

I know how to do this if it was just copying and pasting on to a workbook sheet. but unsure how to use ListObjects and tables in VBA

Any help would be appreciated

Thanks

 

[McMasher]

Since you are pasting into a table, you should be able to just paste the values you want at the bottom of the table and excel will automatically expand the table range

Range("tablename[FirstFieldName]").End(xlDown).Offset(1,0).PasteSpecial Paste:=xlPasteValues or xlPasteFormulas

 

[RandomPezzer]

This is what I created and was using but not sure how to adapt it to work with excel tables.

Private Sub cmdAdd_Click()
Dim i As Integer
Dim LastRow, CopyRef As Long
Dim CopyFromSht, CopyToSht As Worksheet


Application.ScreenUpdating = False


If Workbooks.Open(ThisWorkbook.Path & "\Report.xlsx").ReadOnly = True Then
    Workbooks("Report.xlsx").Close False
    MsgBox ("Report is currently being access, please try again later")
    Exit Sub
End If


Set CopyToSht = Workbooks("Report.xlsx").Sheets("Report")
Set CopyFromSht = Workbooks("Add To Report.xlsm").Sheets("Add")


CopyRef = CopyFromSht.Range("C8").value


With CopyToSht


    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To LastRow
        If .Cells(i, 2).value = CopyRef And .Cells(i, 3).value = CopyFromSht.Range("D8").value Then
            Workbooks("Report.xlsx").Close True
            MsgBox ("Reference Exist!")
            Exit Sub
        End If
    Next i
       
    CopyFromSht.Range("B8:D8").Copy Destination:=CopyToSht.Range("A" & LastRow + 1)
    Workbooks("Report.xlsx").Close True


End With


Application.ScreenUpdating = True


End Sub

 

[RandomPezzer]

I have sorted it now. Figured out how to adjust the code to work