Copy/Paste values only- Autofilter

[aadil_1408]

Hello,

I have written a code to take value form a sheet (Database) and transfer to another sheet based on owner criteria using auto filter. Is there a way to copy only the values from the database and not formulas as i would like to preserve the copied values.

Attached is the code i am using

 

[aadil_1408]

Sub AadilData()
'
' AadilData Macro
'

'
Dim wsData      As Worksheet
Dim wsDest      As Worksheet
Dim tr As Long

Application.ScreenUpdating = False

Set wsData = Project_Data.Sheets("Database")
Set wsDest = Project_Data.Sheets("Aadil")

tr = wsData.Cells(Rows.Count, "A").End(xlUp).Row
If wsData.FilterMode Then wsData.ShowAllData

With wsData.Rows(1)
    .AutoFilter field:=4, Criteria1:="Aadil"
    If wsData.Range("D1:D" & tr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        wsData.Range("A2:M" & tr).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A" & Rows.Count).End(3)(2)
        wsDest.UsedRange.Borders.ColorIndex = xlNone
        wsDest.Select
    End If
    .AutoFilter field:=4
End With
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Try the amended code below

Sub AadilData()
'
' AadilData Macro
'

'
Dim wsData      As Worksheet
Dim wsDest      As Worksheet
Dim tr As Long

Application.ScreenUpdating = False

Set wsData = Project_Data.Sheets("Database")
Set wsDest = Project_Data.Sheets("Aadil")

tr = wsData.Cells(Rows.Count, "A").End(xlUp).Row
If wsData.FilterMode Then wsData.ShowAllData

With wsData.Rows(1)
    .AutoFilter field:=4, Criteria1:="Aadil"
    If wsData.Range("D1:D" & tr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        wsData.Range("A2:M" & tr).SpecialCells(xlCellTypeVisible).Copy
        wsDest.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
        wsDest.UsedRange.Borders.ColorIndex = xlNone
        wsDest.Select
    End If
    .AutoFilter field:=4
End With
Application.ScreenUpdating = True
End Sub

 

[aadil_1408]

Thanks a lot,
The code worked perfectly

 

[MARK858]

You're welcome