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
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