Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Problem with AutoFilter
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Problem with AutoFilter
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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Problem with AutoFilter
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 wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Problem with AutoFilter
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" wrote:

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 wrote:

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Problem with AutoFilter
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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 24
Default Problem with AutoFilter
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" wrote:

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Problem with AutoFilter
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.

John Quinn wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Problem with AutoFilter
Ps. That line looks fine. It could have been a mistake on the previous line
(or lines).

John Quinn wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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


--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofilter Problem Oscar Excel Discussion (Misc queries) 2 February 11th 10 03:51 PM
PROBLEM WITH AUTOFILTER OMIDF New Users to Excel 1 August 18th 06 05:41 PM
autofilter problem Karmen New Users to Excel 0 February 15th 06 01:43 PM
Problem with autofilter in VBA godgo Excel Programming 1 January 14th 04 01:33 PM
AutoFilter Problem longbow Excel Programming 3 November 24th 03 06:35 AM


All times are GMT +1. The time now is 01:00 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement