[excel] Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table

I am using AutoFilter to sort through a table in VBA, which results in a smaller table of data. I only want to copy/paste the visible cells of one column after the filter is applied. Also, I would like to average the filtered values of one column and put the result in a different cell.

I've found this snippet on Stack which allows me to copy/paste the entire visible results of the filter, but I don't know how to modify it or another way to get only one column's worth of data (without the header) from it.

Range("A1",Cells(65536,Cells(1,256).End(xlToLeft).Column).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

Addition to answer (to calculate with the filtered values):

tgt.Range("B2").Value =WorksheetFunction.Average(copyRange.SpecialCells(xlCellTypeVisible))

I've found this to work very well. It uses the .range property of the .autofilter object, which seems to be a rather obscure, but very handy, feature:

Sub copyfiltered()
    ' Copies the visible columns
    ' and the selected rows in an autofilter
    '
    ' Assumes that the filter was previously applied
    '
    Dim wsIn As Worksheet
    Dim wsOut As Worksheet

    Set wsIn = Worksheets("Sheet1")
    Set wsOut = Worksheets("Sheet2")

    ' Hide the columns you don't want to copy
    wsIn.Range("B:B,D:D").EntireColumn.Hidden = True

    'Copy the filtered rows from wsIn and and paste in wsOut
    wsIn.AutoFilter.Range.Copy Destination:=wsOut.Range("A1")
End Sub

Just to add to Jon's coding if you needed to take it a step further, and do more than just one column you can add something like

Dim copyRange2 As Range
Dim copyRange3 As Range

Set copyRange2 =src.Range("B2:B" & lastRow)
Set copyRange3 =src.Range("C2:C" & lastRow)

copyRange2.SpecialCells(xlCellTypeVisible).Copy tgt.Range("B12")
copyRange3.SpecialCells(xlCellTypeVisible).Copy tgt.Range("C12")

put these near the other codings that are the same you can easily change the Ranges as you need.

I only add this because it was helpful for me. I'd assume Jon already knows this but for those that are less experienced sometimes it's helpful to see how to change/add/modify these codings. I figured since Ruya didn't know how to manipulate the original coding it could be helpful if one ever needed to copy over only 2 visibile columns, or only 3, etc. You can use this same coding, add in extra lines that are almost the same and then the coding is copying over whatever you need.

I don't have enough reputation to reply to Jon's comment directly so I have to post as a new comment, sorry.

Here a code that works with windows office 2010. This script will ask you for input filtered range of cells and then the paste range.

Please, both ranges should have the same number of cells.

Sub Copy_Filtered_Cells()

Dim from As Variant
Dim too As Variant
Dim thing As Variant
Dim cell As Range

'Selection.SpecialCells(xlCellTypeVisible).Select

    'Set from = Selection.SpecialCells(xlCellTypeVisible)
    Set temp = Application.InputBox("Copy Range :", Type:=8)
    Set from = temp.SpecialCells(xlCellTypeVisible)
    Set too = Application.InputBox("Select Paste range selected cells ( Visible cells only)", Type:=8)



    For Each cell In from
        cell.Copy
        For Each thing In too
            If thing.EntireRow.RowHeight > 0 Then
                thing.PasteSpecial
                Set too = thing.Offset(1).Resize(too.Rows.Count)
                Exit For
            End If
        Next
    Next


End Sub

Enjoy!