Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter worksheet based on named range on alternate sheet
I am getting a debug 'Method range of worksheet failed' for the following
macro: Sub MyTeamFilter() Dim c As Range Dim ws As Worksheet Dim iEnd As Long UndoMyTeamFilter Set ws = Sheets("Weekly Performance") iEnd = ws.Range("B4").End(xlDown).Row For Each c In ws.Range("B4:B" & iEnd) If c = ws.Range("[TeamFilter]") Then c.EntireRow.Hidden = True Next c End Sub Teamfilter is a named range on my 'Control' worksheet. I am trying to filter the weekly performance based on the dropdown selected in 'TeamFilter'. Any help would be appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter worksheet based on named range on alternate sheet
Try editing this one line
For Each c In ws.Range("B4:B" & iEnd).Cells "fishy" wrote: I am getting a debug 'Method range of worksheet failed' for the following macro: Sub MyTeamFilter() Dim c As Range Dim ws As Worksheet Dim iEnd As Long UndoMyTeamFilter Set ws = Sheets("Weekly Performance") iEnd = ws.Range("B4").End(xlDown).Row For Each c In ws.Range("B4:B" & iEnd) If c = ws.Range("[TeamFilter]") Then c.EntireRow.Hidden = True Next c End Sub Teamfilter is a named range on my 'Control' worksheet. I am trying to filter the weekly performance based on the dropdown selected in 'TeamFilter'. Any help would be appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter worksheet based on named range on alternate sheet
Oh oops. You also shouldn't need the brackets around your named range in the
line: If c = ws.Range("[TeamFilter]") Then c.EntireRow.Hidden = True "fishy" wrote: I am getting a debug 'Method range of worksheet failed' for the following macro: Sub MyTeamFilter() Dim c As Range Dim ws As Worksheet Dim iEnd As Long UndoMyTeamFilter Set ws = Sheets("Weekly Performance") iEnd = ws.Range("B4").End(xlDown).Row For Each c In ws.Range("B4:B" & iEnd) If c = ws.Range("[TeamFilter]") Then c.EntireRow.Hidden = True Next c End Sub Teamfilter is a named range on my 'Control' worksheet. I am trying to filter the weekly performance based on the dropdown selected in 'TeamFilter'. Any help would be appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter worksheet based on named range on alternate sheet
got further but got another debug.
The named range 'Teamlist' is on the 'control' wrksheet so doesnt recognise the lookup. when i change this to a named range on the same sheet then it looks as if it is working and knows how many rows it should find but doesnt filter them. Help "fishy" wrote: I am getting a debug 'Method range of worksheet failed' for the following macro: Sub MyTeamFilter() Dim c As Range Dim ws As Worksheet Dim iEnd As Long UndoMyTeamFilter Set ws = Sheets("Weekly Performance") iEnd = ws.Range("B4").End(xlDown).Row For Each c In ws.Range("B4:B" & iEnd) If c = ws.Range("[TeamFilter]") Then c.EntireRow.Hidden = True Next c End Sub Teamfilter is a named range on my 'Control' worksheet. I am trying to filter the weekly performance based on the dropdown selected in 'TeamFilter'. Any help would be appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter based on named range cell - debug help required | Excel Programming | |||
How to update a worksheet based on a defined named range using VB. | Excel Programming | |||
Filter Range on Sheet B Based on List on Sheet A | Excel Discussion (Misc queries) | |||
Finding a named range based on cell value and copy/paste to same sheet? | Excel Programming | |||
How Do I Do an Exclude Filter based on a Named Range? | Excel Worksheet Functions |