Forum Discussion

Carl_Stephens's avatar
Carl_Stephens
Copper Contributor
Sep 09, 2020
Solved

How to VBA Copy Paste Special Values

The below code simply copies and pastes the filtered information, and can someone tell me what the below code should look like to copy and paste special values instead of paste only, thanking you in advance.

 

Sub CoypFilteredData()
Dim wsData As Worksheet
Dim wsDest As Worksheet
Dim lr As Long

Application.ScreenUpdating = False

Set wsData = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

lr = wsData.Cells(Rows.Count, "D").End(xlUp).Row

If wsData.FilterMode Then wsData.ShowAllData

With wsData.Rows(1)
.AutoFilter Field:=1, Criteria1:="Carl"
.AutoFilter Field:=2, Criteria1:="<60"
If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
wsData.Range("D2:E" & lr).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("C" & Rows.Count).End(3)(2)
wsDest.Select
End If
.AutoFilter Field:=1
.AutoFilter Field:=2
End With
Application.ScreenUpdating = True
End Sub

  • Carl_Stephens 

    Using Copy and PasteSpecial:

     

    wsData.Range("D2:E" & lr).SpecialCells(xlCellTypeVisible).Copy

    wsDest.Range("C" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues

     

    Please note that this consists of two separate lines.

1 Reply

  • Carl_Stephens 

    Using Copy and PasteSpecial:

     

    wsData.Range("D2:E" & lr).SpecialCells(xlCellTypeVisible).Copy

    wsDest.Range("C" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues

     

    Please note that this consists of two separate lines.

Resources