Macro autofilter, then copy only the visible data and paste in the next available line

Question

So, I have a macro that automatically selects the values ​​in the autofilter based on the date.

This works great. However, I need it to copy only visible cells with data and paste it into the next NEXT line on a sheet called a referral.

Sub Referral() Application.ScreenUpdating = False With Sheets("Raw") Sheets("Raw").ShowAllData Sheets("Raw").Range("A1:BK1").AutoFilter Field:=14, _ Criteria1:=Format(Sheets("Main").Range("E13").Value + 15, "mm/dd/yyyy") Sheets("Raw").Range("A1:BL50000").Copy End With End Sub 

Answer 1

 Sub Referral() Application.ScreenUpdating = False With Sheets("Raw") .ShowAllData .Range("A1:BK1").AutoFilter Field:=14, Criteria1:=Format(Sheets("Main").Range("E13").Value + 15, "mm/dd/yyyy") 'this is generic, you may need to adjust this based on your sheet and data needs Intersect(.UsedRange, .UsedRange.Offset(1)).SpecialCells(xlCellTypeVisible).Copy End With 'goes to cell below last used cell in column A Sheets("referral").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues Sheets("Raw").AutoFilterMode = False Application.ScreenUpdating = True 'don't forget to turn on your ScreenUpdating again! End Sub