Problem with AutoFilter

[John Quinn]

Sub TestIntl()

Dim wkst As Worksheet ' Current Sheet
Dim lbls As Workbook
Dim wslb As Worksheet ' Work Sheet


Set wkst = ActiveSheet
wkst.Selection.AutoFilter Field:=109, Criteria1:="CFGJKLPQRY"
' I also have a second filter FGJKLPQR

Set lbls = Workbooks.Add

lbls.Title = "College Board of Examiners"
lbls.Subject = "Language Comparisons"

Set wslb = ActiveSheet
Selection.AutoFilter Field:=109, Criteria1:="CFGJKLPQRY"
wkst.Columns(217).Copy wslb.Columns(1)
wkst.Columns(93).Copy wslb.Columns(2)
wkst.Columns(81).Copy wslb.Columns(3)
wkst.Columns(7).Copy wslb.Columns(4)

wslb.SaveAs Filename:="C:\CBoETests\CBoE.xls",
FileFormat:=xlNormal

Problems:

' I tried wkst.Selection.AutoFilter Field:=109,
Criteria1:="FGJKLPQR" Method or Data Member not Found
' wkst..AutoFilter Field:=109, Criteria1:="FGJKLPQR"
Method or Data Member not Found
Selection.AutoFilter Field:=109, Criteria1:="CFGJKLPQRY" AutoFilter
Method of Range Class Failed

' The New WorkBook I am sending to the College Board must have only
certain students in it.

' If I remove the Filter, it works great!


End Sub

Thanks for Assistance

J.Q.

 

[Dave Peterson]

Untested. But it did compile:

Option Explicit
Sub TestIntl()

Dim wkst As Worksheet
Dim lbls As Workbook
Dim wslb As Worksheet

Set wkst = ActiveSheet

Set lbls = Workbooks.Add(1) 'single worksheet
Set wslb = lbls.Worksheets(1)
lbls.Title = "College Board of Examiners"
lbls.Subject = "Language Comparisons"

With wkst
'remove any existing filter.
.AutoFilterMode = False
'filter on a single column
.Columns(109).AutoFilter field:=1, Criteria1:="CFGJKLPQRY"
.Columns(217).Copy wslb.Columns(1)
.Columns(93).Copy wslb.Columns(2)
.Columns(81).Copy wslb.Columns(3)
.Columns(7).Copy wslb.Columns(4)
End With

wslb.SaveAs Filename:="C:\CBoETests\CBoE.xls", FileFormat:=xlNormal

End Sub

============
You may want to look at using advanced filter to show just your specific list of
names.

I sometimes cheat.

I'll add a helper column with formulas like:
=or(a2={"Jones","Smith","Quinn","Johnson"})

Then I filter to show just the True's.

 

[John Quinn]

Dave;
I just tried this and it destroyed all of my Macros again. This
keeps happening and I do not know why?

Thanks for getting back to me!

 

[Dave Peterson]

What does destroyed all my macros mean?

Maybe your workbook is on the cusp of corruption--if you run this macro in a
brand new workbook, does it do any damage (not counting the code itself
<bg>)????

 

[John Quinn]

The Contents of the Macros are gone, no instructions. The Toolbar still has
them being display, but when you click on anyone, it says Macro not found.

I have re-keyed everything and I am still getting an error on the line below:

..Columns(109).AutoFilter field:=1, Criteria1:="CFGJKLPQRY"

Application-Defined or Object-defined Error.

Thanks for your help!

J.Q.

 

[Dave Peterson]

I have no idea why your macros disappeared--there's nothing in the code I posted
that would do that.

Did you test it against a brand new workbook?

And if you retyped that code, then I think you made a typo. Just copy|paste
into the code window.

The Contents of the Macros are gone, no instructions. The Toolbar still has
them being display, but when you click on anyone, it says Macro not found.

I have re-keyed everything and I am still getting an error on the line below:

.Columns(109).AutoFilter field:=1, Criteria1:="CFGJKLPQRY"

Application-Defined or Object-defined Error.

Thanks for your help!

J.Q.

 

[Dave Peterson]

Ps. That line looks fine. It could have been a mistake on the previous line
(or lines).

The Contents of the Macros are gone, no instructions. The Toolbar still has
them being display, but when you click on anyone, it says Macro not found.

I have re-keyed everything and I am still getting an error on the line below:

.Columns(109).AutoFilter field:=1, Criteria1:="CFGJKLPQRY"

Application-Defined or Object-defined Error.

Thanks for your help!

J.Q.

 

[John Quinn]

Dave;
Found a lot of examples going through the users group the past three
hours, still stuck but I reposted with code that is a little easier to
follow. I just do not have the knowledge and the books I purchased have been
useless!

Thanks

John

 

[Dave Peterson]

Tom Ogilvy found your typo in your other thread. (I didn't notice it.)

Sometimes (always???), it's better to copy from the post and paste into the code
window.

Dave;
Found a lot of examples going through the users group the past three
hours, still stuck but I reposted with code that is a little easier to
follow. I just do not have the knowledge and the books I purchased have been
useless!

Thanks

John