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

by

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" & src.Rows.Count).End(xlUp).Row

    ' the range that we are auto-filtering (all columns)
    Set filterRange = src.Range("A1:C" & 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" & 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.

More Related Contents:

  1. Copy Paste Values only( xlPasteValues )
  2. In Excel VBA, how do I save / restore a user-defined filter?
  3. Get text from clipboard using GetText – avoid error on empty clipboard
  4. Excel macro – paste only non empty cells from one sheet to another
  5. VBA copy rows that meet criteria to another sheet
  6. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  7. How do I use FileSystemObject in VBA?
  8. Is the . in .Range necessary when defined by .Cells?
  9. Run Excel Macro from Outside Excel Using VBScript From Command Line
  10. Importing text file into excel sheet
  11. How to put a tooltip on a user-defined function
  12. If WorkSheet(“wsName”) Exists [duplicate]
  13. How to copy sheets to another workbook using vba?
  14. Autofilter with column formatted as date
  15. hiding formulas in formula bar
  16. Excel VBA: getting row of clicked button [duplicate]
  17. VBA Many buttons point to the same _Click sub
  18. Excel VBA Filter Change event handler
  19. How to suppress Update Links warning?
  20. How do we use restful APIs from Excel macros (vba)?
  21. Cannot VBA write data to cells in Excel 2007/2010 within a function
  22. How to send email to multiple recipients with addresses stored in Excel?
  23. What are the benefits of using Classes in VBA? [closed]
  24. Writing a VLOOKUP function in vba
  25. Handle cancellation of InputBox to select range
  26. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  27. VBA: How to combine two ranges on different sheets into one, to loop through
  28. Create folder path if does not exist (saving from VBA)
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment