Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copy every thing even if autofiltered (Excel 2000)

    Hello to every lounger out there,

    I face an issue of using my macro to copy som data.

    the macro work fine but when it moves to a work sheet which has an autofilter applied to it, it only copys what is visble by the autofilter,

    any one know a way that I can copy every thing even if hidden by an autofiler or will i need to push the autofilter open?

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

    Re: copy every thing even if autofiltered (Excel 2000)

    I think you'll have to turn off the filter before copying.

    If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    End If

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy every thing even if autofiltered (Excel 2000)

    is there a way to turn the auto filter back on after?

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

    Re: copy every thing even if autofiltered (Excel 2000)

    You'd have to store all autofilter settings in variables before turning autofilter off. I wouldn't bother if I were you.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy every thing even if autofiltered (Excel 2000)

    What if iknow the setting I would like to turn on after, NON blanks?

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

    Re: copy every thing even if autofiltered (Excel 2000)

    On one column? All columns? Some columns?

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy every thing even if autofiltered (Excel 2000)

    one column,
    colume E

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

    Re: copy every thing even if autofiltered (Excel 2000)

    Assuming your table starts in cell A1:

    Range("A1").AutoFilter Field:=5, Criteria1:="<>"

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

    Re: copy every thing even if autofiltered (Excel 2000)

    I had this problem once before.
    Are you using CurrentRegion.Copy?
    If you know the precise data range you want to copy you could use an array instead of turning autofilters off then back on.
    something like

    myBlock = [a4:w260]
    ...
    'move to workbook and sheet where you want the copy, then use the exact reverse..
    [a4:w260] = myBlock

    (adjust the range accordingly)

    Note: this copies values only (which is what you probably have if you are using autofilters??)

    zeddy

Posting Permissions

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