Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variant sheet name to filter
Morning all.
A while back Don Guillett provided me with a filter routine that has been working really well-- with a single exception. My sheet names that I'm calling to don't always remain constant. I.e., sometimes it'll be Sum, with others it'll be Summary, SUMMARY, etc..... I'd like to get the routine to accept all variations of Sum, without me having to rewrite the elements to take into account the different name each time I run the routine in a new workbook. Here's the code. ------------------------------------------------------------------------ Sub FilterA() Dim wks As Worksheet 'I added this because I wanted to get a name variable 'for a worksheet mv = Range("f3").End(xlDown).value ' this sets the criteria. Select Case wks.Name 'I looked at another macro that I have and see that it used Select to account for 'all name variables. Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " 'this is the code that I pulled from the other macro accounting for the name 'variables. With wks Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv 'the original version that Don G. provided gave 'Sheets("Sum")....... It's here that I need to do the variable name. mv1 = Range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. 'And here too to account for variable sheet name. 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 End Sub ------------------------------------------------------------ I had thought this would work, but it throws a 91 error-- object variable or with block not set. What do I need to do to set the object variable? I'm assuming that is my issue. Thank you. Best, SteveB. I'm running Excel 2007. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
variant sheet name to filter
wks is an object variable (a worksheet). You have to assign it to a specific
worksheet using the SET command, or you can use it as follows to represent each worksheet in turn within a loop: Sub FilterA() Dim wks As Worksheet mv = Range("f3").End(xlDown).Value ' this sets the criteria. For Each wks In Worksheets wks.Activate Select Case wks.Name Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " With wks .Range("A8:F8").AutoFilter field:=1, Criteria1:=mv mv1 = Range("a3").End(xlDown).Value .Range("A8:F8").AutoFilter field:=3, Criteria1:=mv1 End With Case Else 'do nothing End Select Next wks End Sub Hope this helps, Hutch "SteveDB1" wrote: Morning all. A while back Don Guillett provided me with a filter routine that has been working really well-- with a single exception. My sheet names that I'm calling to don't always remain constant. I.e., sometimes it'll be Sum, with others it'll be Summary, SUMMARY, etc..... I'd like to get the routine to accept all variations of Sum, without me having to rewrite the elements to take into account the different name each time I run the routine in a new workbook. Here's the code. ------------------------------------------------------------------------ Sub FilterA() Dim wks As Worksheet 'I added this because I wanted to get a name variable 'for a worksheet mv = Range("f3").End(xlDown).value ' this sets the criteria. Select Case wks.Name 'I looked at another macro that I have and see that it used Select to account for 'all name variables. Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " 'this is the code that I pulled from the other macro accounting for the name 'variables. With wks Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv 'the original version that Don G. provided gave 'Sheets("Sum")....... It's here that I need to do the variable name. mv1 = Range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. 'And here too to account for variable sheet name. 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 End Sub ------------------------------------------------------------ I had thought this would work, but it throws a 91 error-- object variable or with block not set. What do I need to do to set the object variable? I'm assuming that is my issue. Thank you. Best, SteveB. I'm running Excel 2007. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
variant sheet name to filter
I think I'd do something like this
if lcase(ws.name) like "sum*" then 'Do what you want to do end if -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "SteveDB1" wrote: Morning all. A while back Don Guillett provided me with a filter routine that has been working really well-- with a single exception. My sheet names that I'm calling to don't always remain constant. I.e., sometimes it'll be Sum, with others it'll be Summary, SUMMARY, etc..... I'd like to get the routine to accept all variations of Sum, without me having to rewrite the elements to take into account the different name each time I run the routine in a new workbook. Here's the code. ------------------------------------------------------------------------ Sub FilterA() Dim wks As Worksheet 'I added this because I wanted to get a name variable 'for a worksheet mv = Range("f3").End(xlDown).value ' this sets the criteria. Select Case wks.Name 'I looked at another macro that I have and see that it used Select to account for 'all name variables. Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " 'this is the code that I pulled from the other macro accounting for the name 'variables. With wks Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv 'the original version that Don G. provided gave 'Sheets("Sum")....... It's here that I need to do the variable name. mv1 = Range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. 'And here too to account for variable sheet name. 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 End Sub ------------------------------------------------------------ I had thought this would work, but it throws a 91 error-- object variable or with block not set. What do I need to do to set the object variable? I'm assuming that is my issue. Thank you. Best, SteveB. I'm running Excel 2007. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
variant sheet name to filter
Thanks Tom,
The for loop was in fact in the code I pulled the select code from, but I used it to run through all the worksheets in a workbook, so I thought it was superfluous, and didn't draw it out. I just tried it and it works exactly as I wanted. Best. "Tom Hutchins" wrote: wks is an object variable (a worksheet). You have to assign it to a specific worksheet using the SET command, or you can use it as follows to represent each worksheet in turn within a loop: Sub FilterA() Dim wks As Worksheet mv = Range("f3").End(xlDown).Value ' this sets the criteria. For Each wks In Worksheets wks.Activate Select Case wks.Name Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " With wks .Range("A8:F8").AutoFilter field:=1, Criteria1:=mv mv1 = Range("a3").End(xlDown).Value .Range("A8:F8").AutoFilter field:=3, Criteria1:=mv1 End With Case Else 'do nothing End Select Next wks End Sub Hope this helps, Hutch "SteveDB1" wrote: Morning all. A while back Don Guillett provided me with a filter routine that has been working really well-- with a single exception. My sheet names that I'm calling to don't always remain constant. I.e., sometimes it'll be Sum, with others it'll be Summary, SUMMARY, etc..... I'd like to get the routine to accept all variations of Sum, without me having to rewrite the elements to take into account the different name each time I run the routine in a new workbook. Here's the code. ------------------------------------------------------------------------ Sub FilterA() Dim wks As Worksheet 'I added this because I wanted to get a name variable 'for a worksheet mv = Range("f3").End(xlDown).value ' this sets the criteria. Select Case wks.Name 'I looked at another macro that I have and see that it used Select to account for 'all name variables. Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " 'this is the code that I pulled from the other macro accounting for the name 'variables. With wks Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv 'the original version that Don G. provided gave 'Sheets("Sum")....... It's here that I need to do the variable name. mv1 = Range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. 'And here too to account for variable sheet name. 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 End Sub ------------------------------------------------------------ I had thought this would work, but it throws a 91 error-- object variable or with block not set. What do I need to do to set the object variable? I'm assuming that is my issue. Thank you. Best, SteveB. I'm running Excel 2007. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
variant sheet name to filter
Barb,
So, I'm guessing then that I'd wrap the If statement around the code I'm using, and the (sum*) will read any variation of the word in the worksheets.name? if lcase(ws.name) like "sum*" then 'Do what you want to do end if "Barb Reinhardt" wrote: I think I'd do something like this if lcase(ws.name) like "sum*" then 'Do what you want to do end if -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "SteveDB1" wrote: Morning all. A while back Don Guillett provided me with a filter routine that has been working really well-- with a single exception. My sheet names that I'm calling to don't always remain constant. I.e., sometimes it'll be Sum, with others it'll be Summary, SUMMARY, etc..... I'd like to get the routine to accept all variations of Sum, without me having to rewrite the elements to take into account the different name each time I run the routine in a new workbook. Here's the code. ------------------------------------------------------------------------ Sub FilterA() Dim wks As Worksheet 'I added this because I wanted to get a name variable 'for a worksheet mv = Range("f3").End(xlDown).value ' this sets the criteria. Select Case wks.Name 'I looked at another macro that I have and see that it used Select to account for 'all name variables. Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " 'this is the code that I pulled from the other macro accounting for the name 'variables. With wks Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv 'the original version that Don G. provided gave 'Sheets("Sum")....... It's here that I need to do the variable name. mv1 = Range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. 'And here too to account for variable sheet name. 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 End Sub ------------------------------------------------------------ I had thought this would work, but it throws a 91 error-- object variable or with block not set. What do I need to do to set the object variable? I'm assuming that is my issue. Thank you. Best, SteveB. I'm running Excel 2007. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
variant sheet name to filter
Correct. It will look for anything beginning with SUM (or Sum or sum or Sum
... .you get the picture. -- HTH, Barb Reinhardt "SteveDB1" wrote: Barb, So, I'm guessing then that I'd wrap the If statement around the code I'm using, and the (sum*) will read any variation of the word in the worksheets.name? if lcase(ws.name) like "sum*" then 'Do what you want to do end if "Barb Reinhardt" wrote: I think I'd do something like this if lcase(ws.name) like "sum*" then 'Do what you want to do end if -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "SteveDB1" wrote: Morning all. A while back Don Guillett provided me with a filter routine that has been working really well-- with a single exception. My sheet names that I'm calling to don't always remain constant. I.e., sometimes it'll be Sum, with others it'll be Summary, SUMMARY, etc..... I'd like to get the routine to accept all variations of Sum, without me having to rewrite the elements to take into account the different name each time I run the routine in a new workbook. Here's the code. ------------------------------------------------------------------------ Sub FilterA() Dim wks As Worksheet 'I added this because I wanted to get a name variable 'for a worksheet mv = Range("f3").End(xlDown).value ' this sets the criteria. Select Case wks.Name 'I looked at another macro that I have and see that it used Select to account for 'all name variables. Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " 'this is the code that I pulled from the other macro accounting for the name 'variables. With wks Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv 'the original version that Don G. provided gave 'Sheets("Sum")....... It's here that I need to do the variable name. mv1 = Range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. 'And here too to account for variable sheet name. 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 End Sub ------------------------------------------------------------ I had thought this would work, but it throws a 91 error-- object variable or with block not set. What do I need to do to set the object variable? I'm assuming that is my issue. Thank you. Best, SteveB. I'm running Excel 2007. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
variant sheet name to filter
cool.
thanks. Best. "Barb Reinhardt" wrote: Correct. It will look for anything beginning with SUM (or Sum or sum or Sum .. .you get the picture. -- HTH, Barb Reinhardt "SteveDB1" wrote: Barb, So, I'm guessing then that I'd wrap the If statement around the code I'm using, and the (sum*) will read any variation of the word in the worksheets.name? if lcase(ws.name) like "sum*" then 'Do what you want to do end if "Barb Reinhardt" wrote: I think I'd do something like this if lcase(ws.name) like "sum*" then 'Do what you want to do end if -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "SteveDB1" wrote: Morning all. A while back Don Guillett provided me with a filter routine that has been working really well-- with a single exception. My sheet names that I'm calling to don't always remain constant. I.e., sometimes it'll be Sum, with others it'll be Summary, SUMMARY, etc..... I'd like to get the routine to accept all variations of Sum, without me having to rewrite the elements to take into account the different name each time I run the routine in a new workbook. Here's the code. ------------------------------------------------------------------------ Sub FilterA() Dim wks As Worksheet 'I added this because I wanted to get a name variable 'for a worksheet mv = Range("f3").End(xlDown).value ' this sets the criteria. Select Case wks.Name 'I looked at another macro that I have and see that it used Select to account for 'all name variables. Case "Sum", "Summary", "SUM", "summary", "SUM ", "Sum ", "Summary ", "SUMMARY", "SUMMARY " 'this is the code that I pulled from the other macro accounting for the name 'variables. With wks Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv 'the original version that Don G. provided gave 'Sheets("Sum")....... It's here that I need to do the variable name. mv1 = Range("a3").End(xlDown).value 'this is my add-on to set a second criteria filter- Name of owner. 'And here too to account for variable sheet name. 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 End Sub ------------------------------------------------------------ I had thought this would work, but it throws a 91 error-- object variable or with block not set. What do I need to do to set the object variable? I'm assuming that is my issue. Thank you. Best, SteveB. I'm running Excel 2007. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Particulars data Filter from sheet 1 to sheet 2 automatically | Excel Worksheet Functions | |||
Filter Range on Sheet B Based on List on Sheet A | Excel Discussion (Misc queries) | |||
'Copy to' Advance Filter depend only on sheet ID not start sheet | Excel Worksheet Functions | |||
Can you filter sheet of data & on another sheet make four groups under each other? | Excel Programming | |||
Need help with writing variant back to sheet | Excel Programming |