How do I copy visible rows to another sheet with VBA?

---

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.