自动过滤器宏，然后复制可见数据，并粘贴到下一个可用行 [英] Autofilter Macro, then copy visible data ONLY and paste to next available row

问题描述

所以我有一个宏，它基于一个日期自动选择自动过滤器中的值。

So I have a macro that automatically chooses values in an autofilter based on a date.

这很好用。然而，我需要它只复制可见单元格的数据，并将其粘贴到名为referral的工作表中的NEXT可用行。

This works great. However I need it to copy ONLY the visible cells with data and paste it into the NEXT available row in worksheet called "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

推荐答案

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

这篇关于自动过滤器宏，然后复制可见数据，并粘贴到下一个可用行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！