Thread: Filter, copy specific columns, and paste to another sheet as VALUES ONLY

Hi guys, I know this must be a repetitive subject to you, but I just can´t make this work despite all the posts I read, so here it goes, and I really appreciate your help.

I took a code from this post: http://www.excelforum.com/excel-prog...ml#post2356294
I find it really helpfull, but now I need to copy only the values... not the formulas.

My code is as follows:

Dim wsSource As Worksheet, wsOutput As Worksheet, rngData As Range
Set wsSource = Sheets("1- Status")
Set wsOutput = Sheets("HR")
With wsSource
Set rngData = .AutoFilter.Range
If rngData.Columns(5).SpecialCells(xlCellTypeVisible).Count = 2 Then
Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("A:A")).Copy wsOutput.Range("G6")
Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("B:B")).Copy wsOutput.Range("I6")
Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("H:H")).Copy wsOutput.Range("D8")
Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("K:K")).Copy wsOutput.Range("D10")
Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("X:X")).Copy wsOutput.Range("C40")
Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("AF:AF")).Copy wsOutput.Range("B40")
Else
MsgBox "No hay datos para esa póliza, o existe más de 1 póliza con ese número"
End If
End With

Set rngData = Nothing
Set wsOutput = Nothing
Set wsSource = Nothing

As I told before, the code works fine. I just need it to copy the values.

Thanks!

Try this:

Dim wsSource As Worksheet, wsOutput As Worksheet, rngData As Range
Set wsSource = Sheets("1- Status")
Set wsOutput = Sheets("HR")

With wsSource
    Set rngData = .AutoFilter.Range
    If rngData.Columns(5).SpecialCells(xlCellTypeVisible).Count = 2 Then
        Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("A:A")).Copy
            wsOutput.Range("G6").PasteSpecial xlPasteValues
        Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("B:B")).Copy
            wsOutput.Range("I6").PasteSpecial xlPasteValues
        Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("H:H")).Copy
            wsOutput.Range("D8").PasteSpecial xlPasteValues
        Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("K:K")).Copy
            wsOutput.Range("D10").PasteSpecial xlPasteValues
        Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("X:X")).Copy
            wsOutput.Range("C40").PasteSpecial xlPasteValues
        Intersect(rngData.Offset(1).Resize(rngData.Rows.Count - 1), .Range("AF:AF")).Copy
            wsOutput.Range("B40").PasteSpecial xlPasteValues
    Else
        MsgBox "No hay datos para esa póliza, o existe más de 1 póliza con ese número"
    End If
End With

Set rngData = Nothing
Set wsOutput = Nothing
Set wsSource = Nothing