首页 文章

自动过滤宏,然后仅复制可见数据并粘贴到下一个可用行

提问于
浏览 669
1

所以我有一个宏,可以根据日期自动选择自动过滤器中的值 .

这很好用 . 但是我需要它只复制带有数据的可见单元格并将其粘贴到名为“referral”的工作表中的NEXT可用行中 .

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

1 回答

  • 6
    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
    

相关问题