Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy/Paste Autofiltered Data (97)

    Is it possible to copy autofiltered data from one worksheet to another and have the filters copy with the data? (I hope this makes sense). I know I could use an Advanced filter to copy the filtered data to another worksheet, but I want the filter criteria too.
    Thanks,
    Caroline in lala-land

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy/Paste Autofiltered Data (97)

    According to Microsoft (q214306), you can't copy/paste AutoFiltered data. (The KnowledgeBase item is for Excel 2000, but I suppose it is also valid for Excel 97.)

    Perhaps somebody can come up with a solution in VBA; I don't know how to get at the AutoFilter criteria.

    When you copy the entire worksheet containing AutoFiltered data (instead of copying a range of cells), the AutoFilter is copied to the new sheet complete with the imposed criteria.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Autofiltered Data (97)

    I copy and paste autofiltered data all the time. However, only the data which passes the filter test is copied. I am unaware how to copy the filter itself though, asside from copying a whole sheet.

    Also, a single level of summarized data may be copied. I have added the "Select Visible Cells" icon to my toolbar for this purpose.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Copy/Paste Autofiltered Data (97)

    To get at the AutoFilter criteria, turn on the macro recorder and then change some of the AutoFilter criteria.
    Stop recording and then look at the code generated.
    To simplify tasks, you could assign range names to the relevant cells.
    You should then be able to set and retrieve the criteria through VBA

    zeddy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy/Paste Autofiltered Data (97)

    Thanks, zeddy!

    I don't know if the original poster is still interested. Here is a crude macro that still needs a lot of work for a real-life application (especially error-handling). It asks for a source cell and destination cell. The source cell should be somewhere within the AutoFiltered table; the destination cell should be in another worksheet.

    Sub CopyWithAutoFilter()
    Dim rngSrc As Range, rngDst As Range
    Dim af As AutoFilter, f As Filters, i As Integer
    Dim FilterInfo()
    Set rngSrc = Range(InputBox("Source"))
    Set rngSrc = rngSrc.CurrentRegion
    Set rngDst = Range(InputBox("Destination"))
    Set af = rngSrc.Parent.AutoFilter
    Set f = af.Filters
    ReDim FilterInfo(1 To f.Count, 1 To 5)
    For i = 1 To f.Count
    If f(i).On Then
    FilterInfo(i, 1) = f(i).Criteria1
    FilterInfo(i, 3) = f(i).Operator
    If f(i).Operator <> 0 Then
    FilterInfo(i, 2) = f(i).Criteria2
    End If
    End If
    FilterInfo(i, 4) = f(i).On
    Next i
    rngSrc.Copy rngDst
    Set rngDst = rngDst.CurrentRegion
    For i = 1 To f.Count
    If f(i).On Then
    If f(i).Operator <> 0 Then
    rngDst.AutoFilter i, f(i).Criteria1, f(i).Operator, f(i).Criteria2
    Else
    rngDst.AutoFilter i, f(i).Criteria1
    End If
    End If
    Next i
    End Sub

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Copy/Paste Autofiltered Data (97)

    Wow! that looks interesting!
    I shall save this for later use.

    Thanks
    zeddy

  7. #7
    New Lounger
    Join Date
    Jan 2001
    Location
    Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Autofiltered Data (97)

    <P ID="edit" class=small>Edited by WebGenii on 10-Apr-02 23:16.</P>There is a way to print the aotofilter values, see http://j-walk.com/ss/excel/usertips/tip044.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •