+ Reply to Thread
Results 1 to 6 of 6

Turn filters on in multiple sheets

  1. #1
    EstherJ
    Guest

    Turn filters on in multiple sheets

    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

  2. #2
    Ron de Bruin
    Guest

    Re: Turn filters on in multiple sheets

    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




  3. #3
    EstherJ
    Guest

    Re: Turn filters on in multiple sheets

    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

    >
    >
    >


  4. #4
    Ron de Bruin
    Guest

    Re: Turn filters on in multiple sheets

    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

    >>
    >>
    >>




  5. #5
    EstherJ
    Guest

    Re: Turn filters on in multiple sheets

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

    >
    >
    >


  6. #6
    Ron de Bruin
    Guest

    Re: Turn filters on in multiple sheets

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

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1