change filter with criteria back to All
 

I'm looping through workbooks that have filtered data. I'm using
GetObject(workbook name) because I don't want to use any select or activate
methods. I have two criteria set in each workbook. I want to turn off the
criteria on the Include/Exclude field. I'm having trouble changing this
field back to all. So I turned off the autofilter and turned it back on with
the same criteria in the Primary PMO field. However, I'm not getting it to
work.

With FS
.NewSearch
.LookIn = strDir
intCnt = .Execute
For Each varFN In .FoundFiles
Set wkbPMO = GetObject(varFN)
Set wks = wkbPMO.Sheets("Scoring Sheet")
With wks.Rows(1)
Set rFind = .Find("Include / Exclude")
intIncldCol = rFind.Column
Set rFind = .Find("Primary PMO")
intPMOCol = rFind.Column
End With
'========================================
'get value for PMO. turn off autofilter,
'then turn back on and reset the criteria
'for Primary PMO
'========================================
With wks
Set flt = .AutoFilter.Filters(intPMOCol)
strPMO = flt.Criteria1
.AutoFilterMode = False
.Range("A2").AutoFilter field:=intPMOCol, Criteria1:=strPMO
End With
wkbPMO.Close
Set wkbPMO = Nothing
Next
End With

change filter with criteria back to All
 

Why not turn on the macro recorder and set set the filter to all manually.

Turn off the macro recorder and look at the code to see how to set your
filter to All.

--
Regards,
Tom Ogilvy


"Dennis" wrote:

I'm looping through workbooks that have filtered data. I'm using
GetObject(workbook name) because I don't want to use any select or activate
methods. I have two criteria set in each workbook. I want to turn off the
criteria on the Include/Exclude field. I'm having trouble changing this
field back to all. So I turned off the autofilter and turned it back on with
the same criteria in the Primary PMO field. However, I'm not getting it to
work.

With FS
.NewSearch
.LookIn = strDir
intCnt = .Execute
For Each varFN In .FoundFiles
Set wkbPMO = GetObject(varFN)
Set wks = wkbPMO.Sheets("Scoring Sheet")
With wks.Rows(1)
Set rFind = .Find("Include / Exclude")
intIncldCol = rFind.Column
Set rFind = .Find("Primary PMO")
intPMOCol = rFind.Column
End With
'========================================
'get value for PMO. turn off autofilter,
'then turn back on and reset the criteria
'for Primary PMO
'========================================
With wks
Set flt = .AutoFilter.Filters(intPMOCol)
strPMO = flt.Criteria1
.AutoFilterMode = False
.Range("A2").AutoFilter field:=intPMOCol, Criteria1:=strPMO
End With
wkbPMO.Close
Set wkbPMO = Nothing
Next
End With

change filter with criteria back to All
 

Tom,

I'm using GetObject() to work with the file via code. The Select and
Selections methods do not work. The macro recorder uses the the Selection
method such as:
Selection.AutoFilter Field:=30

This changes the Criteria1 value back to All. I've tried everything I can
think of. The only thing that works is turning off the autofilter, set the
the other criteria that I wanted back. However, users may use other criteria
that I may not know. So I have to loop through the other filters, set the
criteria in an array and reset then back. This is so much work, when all I
need to do is set one criteria to All.

"Tom Ogilvy" wrote:

Why not turn on the macro recorder and set set the filter to all manually.

Turn off the macro recorder and look at the code to see how to set your
filter to All.

--
Regards,
Tom Ogilvy


"Dennis" wrote:

I'm looping through workbooks that have filtered data. I'm using
GetObject(workbook name) because I don't want to use any select or activate
methods. I have two criteria set in each workbook. I want to turn off the
criteria on the Include/Exclude field. I'm having trouble changing this
field back to all. So I turned off the autofilter and turned it back on with
the same criteria in the Primary PMO field. However, I'm not getting it to
work.

With FS
.NewSearch
.LookIn = strDir
intCnt = .Execute
For Each varFN In .FoundFiles
Set wkbPMO = GetObject(varFN)
Set wks = wkbPMO.Sheets("Scoring Sheet")
With wks.Rows(1)
Set rFind = .Find("Include / Exclude")
intIncldCol = rFind.Column
Set rFind = .Find("Primary PMO")
intPMOCol = rFind.Column
End With
'========================================
'get value for PMO. turn off autofilter,
'then turn back on and reset the criteria
'for Primary PMO
'========================================
With wks
Set flt = .AutoFilter.Filters(intPMOCol)
strPMO = flt.Criteria1
.AutoFilterMode = False
.Range("A2").AutoFilter field:=intPMOCol, Criteria1:=strPMO
End With
wkbPMO.Close
Set wkbPMO = Nothing
Next
End With