copy every thing even if autofiltered (Excel 2000)

[bluerayz]

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?

[HansV]

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

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

[bluerayz]

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

[HansV]

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

[bluerayz]

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

[HansV]

On one column? All columns? Some columns?

[bluerayz]

one column,
colume E

[HansV]

Assuming your table starts in cell A1:

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

[zeddy]

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