Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter macro
Howdie all.
I have a filter macro that Don Guillett made, and I modified to meet my need. It's worked perfectly up until now..... I use it daily so it's not something I want to do without. My problem is that instead of it setting my destination page filter to the check list at the bottom of the filter drop down, it selects the text filter. It's never happened before now. Part of me is wondering if it has to do with the list of sheet names in my Case statement (Has that list grown too long? I hope not, because the last few names are because I found that some workbooks have two common Sum names, and require a numeric designation with the Sum name). What would cause a response like I've described above? My code is below. Sub FilterA() Dim wks As Worksheet mv = range("f3").End(xlDown).value ' this sets the criteria. For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY ", "SUM79", "SUM189", " SUM79", "SUM189", "SUM79 ", "SUM189 " With wks Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv '"12345" mv1 = range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. Sheets(wks.Name).range("A8:F8").AutoFilter field:=3, Criteria1:=mv1 'this takes in to account the owner name for a filter. End With End Select Next wks End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter macro
Hi Don,
Thank you for your response. you're correct in stating that you didn't do it like I show. As I said-- I made some modifications. what you'd originally given to me was as follows: --------------------------------------------------------------- Dim wks As Worksheet mv = range("f3").End(xlDown).value ' this sets the criteria. For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case "Sum", "Summary" With wks Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv '"12345" End With End Select Next wks 'or some very minor variation from the above. in fact, I changed the range. --------------------------------------------------------------- As I looked through what you'd done, and compared it to the one I'd recorded, I began making some dramatic modifications to match what I needed. And up until this morning, I didn't have any troubles with it at all. While I see the UCase statement is indeed better, there are too many variations of the name Sum for the Left to work. I.e., sadly, there are cases in which there is a space before the word Sum, and I never know when it'll occur. In fact, with the way worksheet names are shown, I've had a multitude of occurrences where I had errors thrown because it couldn't find the sum sheet, and I was looking right at it-- with other macros. After a bit of digging around I realized that there was a space in front of, or after the sheet name. This was why I'd used so many variations of the Case statement choices. Thus, since we have so many files, it'd take months to go through, and rename all of the sum sheets to a common name to resolve that issue. So, back to my question.... How would I prevent the text filter from being set? The filter usually just sets the general filter list in the drop down, once I set my choice from my source sheet. Again, thank you. Best. "Don Guillett" wrote: Only looking at the case statement, I don't see how I would have suggested that. Sub testcase() For Each ws In Worksheets If UCase(Left(ws.Name, 3)) = "SUM" Then MsgBox ws.Name End If Next ws End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "SteveDB1" wrote in message ... Howdie all. I have a filter macro that Don Guillett made, and I modified to meet my need. It's worked perfectly up until now..... I use it daily so it's not something I want to do without. My problem is that instead of it setting my destination page filter to the check list at the bottom of the filter drop down, it selects the text filter. It's never happened before now. Part of me is wondering if it has to do with the list of sheet names in my Case statement (Has that list grown too long? I hope not, because the last few names are because I found that some workbooks have two common Sum names, and require a numeric designation with the Sum name). What would cause a response like I've described above? My code is below. Sub FilterA() Dim wks As Worksheet mv = range("f3").End(xlDown).value ' this sets the criteria. For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY ", "SUM79", "SUM189", " SUM79", "SUM189", "SUM79 ", "SUM189 " With wks Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv '"12345" mv1 = range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. Sheets(wks.Name).range("A8:F8").AutoFilter field:=3, Criteria1:=mv1 'this takes in to account the owner name for a filter. End With End Select Next wks End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter macro
My mistake Don,
It was Tom Hutchins that provided that filter macro to me. I'd posted it back on October 15th. Barb Reinhardt provided another version which I never used. I'd titled my post- variant sheet name to filter. So, please accept my apology for confusing it. Best. "Don Guillett" wrote: Only looking at the case statement, I don't see how I would have suggested that. Sub testcase() For Each ws In Worksheets If UCase(Left(ws.Name, 3)) = "SUM" Then MsgBox ws.Name End If Next ws End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "SteveDB1" wrote in message ... Howdie all. I have a filter macro that Don Guillett made, and I modified to meet my need. It's worked perfectly up until now..... I use it daily so it's not something I want to do without. My problem is that instead of it setting my destination page filter to the check list at the bottom of the filter drop down, it selects the text filter. It's never happened before now. Part of me is wondering if it has to do with the list of sheet names in my Case statement (Has that list grown too long? I hope not, because the last few names are because I found that some workbooks have two common Sum names, and require a numeric designation with the Sum name). What would cause a response like I've described above? My code is below. Sub FilterA() Dim wks As Worksheet mv = range("f3").End(xlDown).value ' this sets the criteria. For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY ", "SUM79", "SUM189", " SUM79", "SUM189", "SUM79 ", "SUM189 " With wks Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv '"12345" mv1 = range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. Sheets(wks.Name).range("A8:F8").AutoFilter field:=3, Criteria1:=mv1 'this takes in to account the owner name for a filter. End With End Select Next wks End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter macro
As a further addendum, I've found that the text filter selection error only
occurs when I select blanks in the filter from my source page. It's as though the filter no longer exists or is only filtering off a single filter selection, yet when I look at the code, nothing's changed from yesterday when it worked fine. Any helps would be appreciated. "SteveDB1" wrote: Howdie all. I have a filter macro that Don Guillett made, and I modified to meet my need. It's worked perfectly up until now..... I use it daily so it's not something I want to do without. My problem is that instead of it setting my destination page filter to the check list at the bottom of the filter drop down, it selects the text filter. It's never happened before now. Part of me is wondering if it has to do with the list of sheet names in my Case statement (Has that list grown too long? I hope not, because the last few names are because I found that some workbooks have two common Sum names, and require a numeric designation with the Sum name). What would cause a response like I've described above? My code is below. Sub FilterA() Dim wks As Worksheet mv = range("f3").End(xlDown).value ' this sets the criteria. For Each wks In ActiveWorkbook.Worksheets Select Case wks.Name Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY ", "SUM79", "SUM189", " SUM79", "SUM189", "SUM79 ", "SUM189 " With wks Sheets(wks.Name).range("A8:F8").AutoFilter field:=1, Criteria1:=mv '"12345" mv1 = range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. Sheets(wks.Name).range("A8:F8").AutoFilter field:=3, Criteria1:=mv1 'this takes in to account the owner name for a filter. End With End Select Next wks End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Filter | Excel Worksheet Functions | |||
Do i use a filter or a macro? | Excel Discussion (Misc queries) | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming | |||
Need macro to filter, create tab on filter and copy/paste | Excel Programming | |||
Need a filter macro | Excel Discussion (Misc queries) |