SOLVED

How to VBA Copy Paste Special Values

%3CLINGO-SUB%20id%3D%22lingo-sub-1650239%22%20slang%3D%22en-US%22%3EHow%20to%20VBA%20Copy%20Paste%20Special%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1650239%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20below%20code%20simply%20copies%20and%20pastes%20the%20filtered%20information%2C%20and%20can%20someone%20tell%20me%20what%20the%20below%20code%20should%20look%20like%20to%20copy%20and%20paste%20special%20values%20instead%20of%20paste%20only%2C%20thanking%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20CoypFilteredData()%3CBR%20%2F%3EDim%20wsData%20As%20Worksheet%3CBR%20%2F%3EDim%20wsDest%20As%20Worksheet%3CBR%20%2F%3EDim%20lr%20As%20Long%3C%2FP%3E%3CP%3EApplication.ScreenUpdating%20%3D%20False%3C%2FP%3E%3CP%3ESet%20wsData%20%3D%20Worksheets(%22Sheet1%22)%3CBR%20%2F%3ESet%20wsDest%20%3D%20Worksheets(%22Sheet2%22)%3C%2FP%3E%3CP%3Elr%20%3D%20wsData.Cells(Rows.Count%2C%20%22D%22).End(xlUp).Row%3C%2FP%3E%3CP%3EIf%20wsData.FilterMode%20Then%20wsData.ShowAllData%3C%2FP%3E%3CP%3EWith%20wsData.Rows(1)%3CBR%20%2F%3E.AutoFilter%20Field%3A%3D1%2C%20Criteria1%3A%3D%22Carl%22%3CBR%20%2F%3E.AutoFilter%20Field%3A%3D2%2C%20Criteria1%3A%3D%22%26lt%3B60%22%3CBR%20%2F%3EIf%20wsData.Range(%22H1%3AH%22%20%26amp%3B%20lr).SpecialCells(xlCellTypeVisible).Cells.Count%20%26gt%3B%201%20Then%3CBR%20%2F%3EwsData.Range(%22D2%3AE%22%20%26amp%3B%20lr).SpecialCells(xlCellTypeVisible).Copy%20wsDest.Range(%22C%22%20%26amp%3B%20Rows.Count).End(3)(2)%3CBR%20%2F%3EwsDest.Select%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E.AutoFilter%20Field%3A%3D1%3CBR%20%2F%3E.AutoFilter%20Field%3A%3D2%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1650239%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1650310%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20VBA%20Copy%20Paste%20Special%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1650310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F787478%22%20target%3D%22_blank%22%3E%40Carl_Stephens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsing%20Copy%20and%20PasteSpecial%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EwsData.Range(%22D2%3AE%22%20%26amp%3B%20lr).SpecialCells(xlCellTypeVisible).Copy%3C%2FP%3E%0A%3CP%3EwsDest.Range(%22C%22%20%26amp%3B%20Rows.Count).End(3)(2).PasteSpecial%20Paste%3A%3DxlPasteValues%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20note%20that%20this%20consists%20of%20two%20separate%20lines.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

1 Reply
Best Response confirmed by Carl_Stephens (Occasional Contributor)
Solution

@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.