vba 自动过滤宏，然后仅复制可见数据并粘贴到下一个可用行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11903878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-08 13:44:52 来源:igfitidea点击:
Autofilter Macro, then copy visible data ONLY and paste to next available row
提问by user1590497
So I have a macro that automatically chooses values in an autofilter based on a date.
所以我有一个宏可以根据日期自动选择自动过滤器中的值。
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
回答by Scott Holtzman
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
