0

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
Gun hanwei
  • 93
  • 1
  • 7
  • You need to copy only the visible cells. See [Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table](https://stackoverflow.com/questions/17531128/copy-paste-calculate-visible-cells-from-one-column-of-a-filtered-table). – Pᴇʜ Jun 02 '21 at 08:49
  • @Pᴇʜ If I use `.SpecialCells(xlCellTypeVisible).Copy` , the header will also being copy, how to skip the header? – Gun hanwei Jun 02 '21 at 09:01
  • Please do some research before asking: [How can I copy the filtered range without the headers and paste it into another sheet?](https://stackoverflow.com/questions/37837916/how-can-i-copy-the-filtered-range-without-the-headers-and-paste-it-into-another) those questions were asked a hundred times. – Pᴇʜ Jun 02 '21 at 09:02

0 Answers0