Setting Autofilter using code

[Guest]

Hi all,

I have a spreadsheet where as part of my macro I'm copying data from one
sheet to another. Because the sheet which is being copied from will have
Autofilter set on row 1 and because at the time the macro runs the data may
be filtered on a particular value, I believe I need to turn off autofilter so
that I can copy all of the cells in the spreadsheet -- I then need to turn
autofilter back on. Currently I'm doing this with the code below. I tried
using Help to see if I could do something like "If Autofilter is set to True,
then set to False...." but wasn't able to find information regarding doing
this. Is there a way to do this? Thanks in advance!

'FYI - The "Open Transactions by Member ID" sheet has Autofilter set prior
to the macro being run

With Sheets("Open Transactions by Member ID")
.Rows("1:1").AutoFilter
.Cells.Copy
End With
With Sheets("Open Trans by Member ID WIP")
.Cells.PasteSpecial (xlPasteAll)
End With
With Sheets("Open Transactions by Member ID")
.Rows("1:1").AutoFilter
End With

 

[Dave Peterson]

You can use this to show all the data.

With Worksheets("Open Transactions by Member ID")
If .FilterMode Then
.ShowAllData
End If
'now do the copy|Paste
End With

 

[Guest]

Thanks Dave!!

--
Robert

You can use this to show all the data.

With Worksheets("Open Transactions by Member ID")
If .FilterMode Then
.ShowAllData
End If
'now do the copy|Paste
End With

 

[Guest]

Hi Dave,

I took the code with a slight variation to have the Autofilter set when it
is not currently set...this code is not working. Any ideas?

With Sheets("Open Transactions by Member ID")
If .FilterMode <> True Then
.Rows("1:1").AutoFilter
Else
End If
End With

 

[Dave Peterson]

..Filtermode indicates if the existing filter has been used--usually some rows
are hidden.

If you want to remove the arrows and reapply them, you can use:

with worksheets("open transactions by member id")
.AutoFilterMode = False
.rows(1).autofilter
end with

Although, I would be more explicit with the range to filter.

Dim LastRow as long
Dim LastCol as long
with worksheets("open transactions by member id")
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column
.AutoFilterMode = False
.range("A1",.cells(lastrow,lastcol)).autofilter
end with

or even

with worksheets("open transactions by member id")
.AutoFilterMode = False
.usedrange.columns.autofilter
end with

Hi Dave,

I took the code with a slight variation to have the Autofilter set when it
is not currently set...this code is not working. Any ideas?

With Sheets("Open Transactions by Member ID")
If .FilterMode <> True Then
.Rows("1:1").AutoFilter
Else
End If
End With