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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 02:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com