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
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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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" <[email protected]> wrote in message news:[email protected]...
>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" <[email protected]> wrote in message news:[email protected]...
>> > 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" <[email protected]> wrote in message news:[email protected]...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks