Copy Paste Calculate Visible Cells from One Column of a Filtered Table

Question

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

User · Answer

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  gt  0 Then                 thing PasteSpecial                 Set too   thing Offset 1  Resize too Rows Count                  Exit For             End If         Next     Next   End Sub   Enjoy

User · Answer

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

User · Answer

I set up a simple 3-column range on Sheet1 with Country  City  and Language in columns A  B  and C  The following code autofilters the range and then pastes only one of the columns of autofiltered data to another sheet  You should be able to modify this for your purposes   Sub CopyPartOfFilteredRange       Dim src As Worksheet     Dim tgt As Worksheet     Dim filterRange As Range     Dim copyRange As Range     Dim lastRow As Long      Set src   ThisWorkbook Sheets  Sheet1       Set tgt   ThisWorkbook Sheets  Sheet2          turn off any autofilters that are already set     src AutoFilterMode   False        find the last row with data in column A     lastRow   src Range  A   amp  src Rows Count  End xlUp  Row        the range that we are auto-filtering  all columns      Set filterRange   src Range  A1 C   amp  lastRow         the range we want to copy  only columns we want to copy        in this case we are copying country from column A       we set the range to start in row 2 to prevent copying the header     Set copyRange   src Range  A2 A   amp  lastRow         filter range based on column B     filterRange AutoFilter field  2  Criteria1   Rio de Janeiro         copy the visible cells to our target range       note that you can easily find the last populated row on this sheet       if you don t want to over-write your previous results     copyRange SpecialCells xlCellTypeVisible  Copy tgt Range  A1    End Sub   Note that by using the syntax above to copy and paste  nothing is selected or activated  which you should always avoid in Excel VBA  and the clipboard is not used  As a result  Application CutCopyMode   False is not necessary

User · Answer

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   amp  lastRow  Set copyRange3  src Range  C2 C   amp  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

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

Examples related to excel

Examples related to filter

Examples related to copy-paste

Examples related to vba