You can use the following syntax in VBA to copy only the visible rows from one sheet to another:
Sub CopyVisibleRows()
Dim sourceWS As Worksheet
Dim destinationWS As Worksheet
Set sourceWS = ThisWorkbook.Sheets("Sheet1")
Set destinationWS = ThisWorkbook.Sheets("Sheet2")
sourceWS.Range("A1:D999").SpecialCells(xlCellTypeVisible).Copy
destinationWS.Cells(1, 1).PasteSpecial
Application.CutCopyMode = False
End Sub
This particular macro will copy each visible row in the range A1:D999 of Sheet1 and paste them starting in cell A1 of Sheet2.
Note: The line Application.CutCopyMode = False specifies that the cut and copy mode should be turned off after running the macro.
The following example shows how to use this syntax in practice.
Example: Copy Visible Rows to Another Sheet Using VBA
Suppose we have the following dataset in Sheet1 that contains information about various basketball players:
Now suppose we apply a filter to the dataset to only show the rows where the team name is equal to Mavs or Spurs:
Now suppose we would like to use VBA to only copy the visible cells in Sheet1 to Sheet2.
We can create the following macro to do so:
Sub CopyVisibleRows()
Dim sourceWS As Worksheet
Dim destinationWS As Worksheet
Set sourceWS = ThisWorkbook.Sheets("Sheet1")
Set destinationWS = ThisWorkbook.Sheets("Sheet2")
sourceWS.Range("A1:D999").SpecialCells(xlCellTypeVisible).Copy
destinationWS.Cells(1, 1).PasteSpecial
Application.CutCopyMode = False
End Sub
When we run this macro, we receive the following output in Sheet2:
Notice that each row that was visible in Sheet1 has been copy and pasted into Sheet2.
Note #1: We used xlCellTypeVisible in the macro to specify that we only wanted to copy the cells that were visible.
Note #2: You can find the complete documentation for the VBA Copy method here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Find Last Used Row
VBA: Copy Rows to Another Sheet Based on Criteria
VBA: How to Paste Values Only with No Formatting