您可以使用 AutoFilter 方法,它将节省您在所有行中使用 For 循环的需要,并且只需将整个过滤范围复制到"Tab 2"工作表 .
Code (评论内部评论)
Option Explicit
Sub CopyRow()
Application.ScreenUpdating = False
Dim x As Long
Dim MaxRowList As Long
Dim MaxCol As Long
Dim S As String
Dim aCol As Long
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim SourceRng As Range
Dim VisRng As Range
Set wsSource = ThisWorkbook.Worksheets("Tab 1")
Set wsTarget = ThisWorkbook.Worksheets("Tab 2")
aCol = 1
With wsSource
MaxRowList = .Cells(.Rows.Count, aCol).End(xlUp).Row ' find last row
MaxCol = .Cells(1, .Columns.Count).End(xlToLeft).Column ' find last column
Set SourceRng = .Range(.Cells(1, 1), .Cells(MaxRowList, MaxCol)) ' set source range to actually occupied range
.Range("A1").AutoFilter ' use AutoFilter method
SourceRng.AutoFilter Field:=1, Criteria1:="2016"
Set VisRng = SourceRng.SpecialCells(xlCellTypeVisible) ' set range to filterred range
VisRng.Copy ' copy entire visible range
wsTarget.Range("A2").PasteSpecial xlPasteValues ' past with 1 line
End With
Application.ScreenUpdating = True
End Sub