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