I have 2 sheet. If column W in sheet 1 is not being highlighted, then it need to be copy and paste to the last line of sheet 2. This code is working fine when there are new items to copy. But when there is no unfill (xlFilterNoFill) cell in column W, then it will move all highlighted data to sheet2, which is what I don't want.
Option Explicit
Sub Addnewitem()
Dim ws_NewItem As Worksheet
Set ws_NewItem = Worksheets("new item")
Dim LastRow As Integer
With ws_NewItem
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
ws_NewItem.Range("$A$1:$W$" & LastRow).AutoFilter Field:=23, Operator:=xlFilterNoFill
With ws_NewItem.AutoFilter.Range
.Offset(1, 0).Resize(.Rows.Count - 1).Copy
End With
With Sheets("Sheet 2")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End Sub