Copy/Paste Filtered Data w/ existing Macro

  • Hello all,


    I am utilizing the below macro, but ran into a bit of a snag. Once filtering the data in Sheet1, this copy/paste macro will include the data that I do not want that has been filtered out.


    Is there a different paste code to use other than xlPasteValues that will only copy/paste the data that I need?


    Thank you for your help!


  • Re: Copy/Paste Filtered Data w/ existing Macro


    maybe you can adapt this


    [vba]
    Option Explicit




    '---------------------------------------------------------------------------------------
    ' Module : Demo Copy Filtered Rows
    ' DateTime : 01-12-10
    ' Author : Roy Cox (royUK)
    ' Website : http://www.excel-it.com
    ' Purpose : Create a check by using a Hidden Name to control how often a macro runs
    ' Disclaimer; This code is offered as is with no guarantees. You may use it in your
    ' projects but please leave this header intact.


    '---------------------------------------------------------------------------------------'---------------------------------------------------------------------------------------




    Sub copyVisToSummary()
    Dim ws As Worksheet
    Dim wsMain As Worksheet
    Dim rng As Range
    Dim NextRw As Long


    Set wsMain = Sheets("Summary") '<- this should be the destination sheet, change as needed
    Set ws = Sheets("Data") '<- this should be the copy from sheet, change as needed
    'do some checks
    'check whether a filter Is applied
    With ws
    If Not .AutoFilterMode Then
    MsgBox ws.Name & " is not filtered", vbCritical, "Quitting"
    Exit Sub
    End If
    If Not .FilterMode Then
    MsgBox ws.Name & " has filters, but is not filtered", vbCritical, "Quitting"
    Exit Sub
    End If


    'set a range to include visible cells (excluding the header)
    Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1) _
    .SpecialCells(xlCellTypeVisible)
    End With
    With wsMain
    'get the next empty row in the summary sheet based on Column A
    NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    'copy filtered data to summary
    rng.Copy .Cells(NextRw, 1)
    End With
    End If
    Next ws
    End Sub[/vba]