excelvba

Excel Cut/Paste VBA . Table VS Sheet


Good Afternoon,

Im having issues with creating a simple VBA script that would Cut/Paste from one sheet to another. Problems is data in both sheets are in a table (Tab_Main and Tab_Done). The code below works if there was no table and simply cut pasting data outside the table. For example, When looking up the last available row to paste it (lastCell1), it uses the first empty row below the table instead of within it. Anyway to adapt this code to work?

Simply Put, In Sheet "Main", theres a table named Tab_Main, When i put an "X" in the first column, i want it to be cut and pasted in the second sheet ("Done") inside the table called Tab_Done

lastCell1 = Worksheets("Main").Cells(Rows.Count, 2).End(xlUp).Row

For i = 2 To lastCell1

If Worksheets("A Faire").Cells(i, 1).Value = "X" Then

    Worksheets("Main").Rows(i).Cut
    Worksheets("Done").Activate
    lastCell2 = Worksheets("Done").Cells(Rows.Count, 2).End(xlUp).Row
    Worksheets("Done").Cells(lastCell2 + 1, 2).Select
    ActiveSheet.Paste
    Worksheets("Main").Activate

End If

Next

Solution

  • You can leverage the ListObject methods and properties.

    Try something like this:

    Sub Tester()
        Dim wb As Workbook, wsAF As Worksheet, lrD As ListRow
        Dim tblMain As ListObject, tblDone As ListObject, lrM As ListRow
        
        Set wb = ThisWorkbook 'or some other workbook
        Set wsAF = wb.Worksheets("A Faire")
        
        'Reference listobjects/tables
        Set tblMain = wb.Worksheets("Main").ListObjects("Tab_Main")
        Set tblDone = wb.Worksheets("Done").ListObjects("Tab_Done")
        
        For Each lrM In tblMain.ListRows 'loop each row in mAin taBLE
            If wsAF.Cells(lrM.Range.Row, 1).Value = "X" Then
                Set lrD = tblDone.ListRows.Add         'add new empty row on "Done"
                lrM.Range.Cut Destination:=lrD.Range   'cut the row to the empty row
            End If
        Next lrM
    End Sub