Copying Row from existing sheet based on date range

Question

Below code helps me out, if on sheet1, specific date on column L is less than or equal to today's date, from sheet1 copy specific rows to sheet2 starting with A3 and do it for the whole list.

I have two follow up questions;

1) I want copied cells on sheet2 to be inserted on a newly created row ( need to incorporate End(xlUp) ) . Couldn't figure out yet

2) For some reason code doesn't work if my first sheet have filters on... I have to remove filters for the code. Not sure why it is not working regardless of filters on or off.

     Sub CopyRange()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, 
     SearchDirection:=xlPrevious).Row
Range("L1:L" & LastRow).AutoFilter Field:=1, Criteria1:="<=" & Date
Intersect(Rows("2:" & LastRow), 
     Range("A:A,F:H,K:L,R:R,U:U").SpecialCells(xlCellTypeVisible)).Copy 
     Sheets("Sheet2").Cells(3, 1)
     Range("L1").AutoFilter
   Application.ScreenUpdating = True
 End Sub

GMalc GMalc · Accepted Answer · 2018-09-19T04:15:58

Just use Range("L:L"). Also, I would suggest you use With ActiveSheet or better yet With Worksheet("sheetname")

Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

With Worksheets("Sheet1")
    Range("L:L").AutoFilter Field:=1, Criteria1:="<=" & Date

    Intersect(Rows("2:" & LastRow), _
    Range("A:A,F:H,K:L,R:R,U:U").SpecialCells(xlCellTypeVisible)).Copy _
    Sheets("Sheet2").Cells(3, 1)

    Range("L1").AutoFilter
End With
Application.ScreenUpdating = True

Copying Row from existing sheet based on date range

1 Answers