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

robs3131 wrote:

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

--
Robert

--

Dave Peterson