How to Paste Filtered Values into a Column in Excel using VBA Macro in Excel Formula

In this article, we will learn how to paste filtered values into a column in Excel using VBA macro in excel formula. This is a useful technique when you want to copy and paste only the visible cells from a filtered range, while skipping the hidden cells. For example, you may want to paste a single value or a set of values to all the visible rows of a filtered column, or you may want to copy and paste the filtered values from one column to another.

To paste filtered values into a column in Excel using VBA macro in excel formula, we need to use the following steps:

• Apply a filter to the range of data that we want to copy from, using the AutoFilter method or the Filter button on the Data tab.
• Select the visible cells that match the filter criteria, using the SpecialCells method with the xlCellTypeVisible argument.
• Copy the selected cells, using the Copy method or the Ctrl+C shortcut.
• Select the first cell where we want to paste the copied cells, using the Select method or the mouse.
• Paste the copied cells, using the PasteSpecial method with the xlPasteValues argument or the Ctrl+V shortcut.

Procedures

To illustrate the procedures, we will use a sample data set of employees with their names, departments, salaries, and bonuses. We will paste filtered values into a column in Excel using VBA macro in excel formula in three scenarios:

• Scenario 1: Paste a single value to all the visible rows of a filtered column.
• Scenario 2: Paste a set of values to visible cells of a filtered column.
• Scenario 3: Copy and paste the filtered values from one column to another.

Scenario 1: Paste a single value to all the visible rows of a filtered column

In this scenario, we want to paste the value 10% to the Bonus column for all the employees in the IT department. We will use the following VBA code to achieve this:

Sub PasteSingleValue()
    'Declare variables
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim FilterRange As Range
    
    'Set worksheet variable
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    'Find the last used row and column
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    'Define the filter range
    Set FilterRange = ws.Range("A1", ws.Cells(LastRow, "D"))
    
    'Apply the filter to the Department column
    FilterRange.AutoFilter Field:=2, Criteria1:="IT"
    
    'Paste the value 10% to the visible cells in the Bonus column
    FilterRange.SpecialCells(xlCellTypeVisible).Offset(ColumnOffset:=3).Value = 0.1
    
    'Remove the filter
    FilterRange.AutoFilter
End Sub

The result of running this macro is shown below:

As you can see, only the visible rows in the Bonus column have been updated with the value 10%, while the hidden rows remain unchanged.

Scenario 2: Paste a set of values to visible cells of a filtered column

In this scenario, we want to paste a set of values from another worksheet to the Bonus column for all the employees in the IT department. We will use the following VBA code to achieve this:

Sub PasteSetOfValues()
    'Declare variables
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim LastRow As Long, LastCol As Long
    Dim FilterRange As Range, CopyRange As Range, PasteRange As Range
    Dim c As Range
    
    'Set worksheet variables
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    'Find the last used row and column in Sheet1
    LastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    LastCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column
    
    'Define the filter range in Sheet1
    Set FilterRange = ws1.Range(ws1.Cells(1, 1), ws1.Cells(LastRow, LastCol))
    
    'Apply the filter to the Department column in Sheet1
    FilterRange.AutoFilter Field:=2, Criteria1:="IT"
    
    'Define the copy range in Sheet2
    Set CopyRange = ws2.Range("A1", ws2.Cells(LastRow, 1))
    
    'Define the paste range in Sheet1
    Set PasteRange = FilterRange.Columns(4)
    
    'Loop through each visible cell in the paste range
    For Each c In PasteRange.SpecialCells(xlCellTypeVisible)
        'Paste the value from the copy range to the paste range
        c.Value = CopyRange.Cells(c.Row, 1).Value
    Next c
    
    'Remove the filter
    FilterRange.AutoFilter
End Sub

The result of running this macro is shown below:

As you can see, the values from Sheet2 have been pasted to the visible cells in the Bonus column, while the hidden cells remain unchanged.

Scenario 3: Copy and paste the filtered values from one column to another

In this scenario, we want to copy and paste the filtered values from the Salary column to the Bonus column for all the employees in the IT department. We will use the following VBA code to achieve this:

Sub CopyPasteFilteredValues()
    'Declare variables
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim FilterRange As Range
    
    'Set worksheet variable
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    'Find the last used row
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    'Define the filter range
    Set FilterRange = ws.Range("A1", ws.Cells(LastRow, "D"))
    
    'Apply the filter to the Department column
    FilterRange.AutoFilter Field:=2, Criteria1:="IT"
    
    'Copy the visible cells in the Salary column
    FilterRange.Columns(3).SpecialCells(xlCellTypeVisible).Copy
    
    'Paste the copied cells to the Bonus column
    FilterRange.Columns(4).PasteSpecial xlPasteValues
    
    'Remove the filter
    FilterRange.AutoFilter
End Sub

The result of running this macro is shown below:

As you can see, the filtered values from the Salary column have been pasted to the Bonus column, while the hidden cells remain unchanged.