What is the code for turning the auto filter on in all the sheets in a
workbook (headers are on row 2) and have them filter for the same criteria
"JS" in column 2.

Thank you for your help,

Esther

Try this

Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A2").CurrentRegion.AutoFilter Field:=2, Criteria1:="JS"
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
What is the code for turning the auto filter on in all the sheets in a
workbook (headers are on row 2) and have them filter for the same criteria
"JS" in column 2.

Thank you for your help,

Esther

It works but needs some tweaking. It brings up an error message saying
"Autofilter method of Range class failed" and the headers (Row 2) and the
drop down arrows are hidden. Any ideas.

Thanks,

Esther

"Ron de Bruin" wrote:

Try this

Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A2").CurrentRegion.AutoFilter Field:=2, Criteria1:="JS"
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
What is the code for turning the auto filter on in all the sheets in a
workbook (headers are on row 2) and have them filter for the same criteria
"JS" in column 2.

Thank you for your help,

Esther

hi Esther

Is row 1 empty ?
If you want you can send me your test workbook private then I look at it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
It works but needs some tweaking. It brings up an error message saying
"Autofilter method of Range class failed" and the headers (Row 2) and the
drop down arrows are hidden. Any ideas.

Thanks,

Esther

"Ron de Bruin" wrote:

Try this

Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A2").CurrentRegion.AutoFilter Field:=2, Criteria1:="JS"
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
What is the code for turning the auto filter on in all the sheets in a
workbook (headers are on row 2) and have them filter for the same criteria
"JS" in column 2.

Thank you for your help,

Esther

I think I have solved the problems. The error message I think was due to a
blank sheet. The hidden column headers (row 2) was to do with row 1. As it
was not blank the range included row 1 so I have added the offset code and
all seems OK.

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A2").CurrentRegion.Offset(rowoffset:=1,
columnoffset:=0).AutoFilter Field:=2, Criteria1:="JS"
Next
End Sub

Thank you for you help.
Esther

"Ron de Bruin" wrote:

hi Esther

Is row 1 empty ?
If you want you can send me your test workbook private then I look at it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
It works but needs some tweaking. It brings up an error message saying
"Autofilter method of Range class failed" and the headers (Row 2) and the
drop down arrows are hidden. Any ideas.

Thanks,

Esther

"Ron de Bruin" wrote:

Try this

Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A2").CurrentRegion.AutoFilter Field:=2, Criteria1:="JS"
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
What is the code for turning the auto filter on in all the sheets in a
workbook (headers are on row 2) and have them filter for the same criteria
"JS" in column 2.

Thank you for your help,

Esther

Thank you for you help
You are welcome

--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
I think I have solved the problems. The error message I think was due to a
blank sheet. The hidden column headers (row 2) was to do with row 1. As it
was not blank the range included row 1 so I have added the offset code and
all seems OK.

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A2").CurrentRegion.Offset(rowoffset:=1,
columnoffset:=0).AutoFilter Field:=2, Criteria1:="JS"
Next
End Sub

Thank you for you help.
Esther

"Ron de Bruin" wrote:

hi Esther

Is row 1 empty ?
If you want you can send me your test workbook private then I look at it

--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
It works but needs some tweaking. It brings up an error message saying
"Autofilter method of Range class failed" and the headers (Row 2) and the
drop down arrows are hidden. Any ideas.

Thanks,

Esther

"Ron de Bruin" wrote:

Try this

Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A2").CurrentRegion.AutoFilter Field:=2, Criteria1:="JS"
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"EstherJ" wrote in message ...
What is the code for turning the auto filter on in all the sheets in a
workbook (headers are on row 2) and have them filter for the same criteria
"JS" in column 2.

Thank you for your help,

Esther

hellow sir,

i need your guidence, i have a unique list in column AI. I Want to
apply auto filter in column D. for each unique items. i want to check the
no of visible rows after filtered. if it is smaller than 30 . i have to
update column J with the current year for all the sks in
the visible rows. if it is greater than 30 . i have to upadted J column
with the current year. 1/3 times of the visible rows.(ex if the visible rows
of unique items is 100, i have to updated 34 rows.) and after doing this, i
have to copy the visible rows for each unique items and save in a new folder
and save the file.

all the unique items must be saved in a new folder and
new file ok.

i will be very happy if you help me.
with regards,
harish.
mail id:

url:http://www.ureader.com/msg/103711524.aspx