Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Won't accept range name
I'm trying to apply an advanced filter to a named range called
"FilterRange" on the source worksheet (RecSht). RecSht.Select Range("a1").Select 'Selection.CurrentRegion.Select Set FilterRange = Selection.CurrentRegion CtyExtr.Select RecSht.Range(FilterRange).AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False I tried using a named range, but it seems that macros don't recognize a named range, at least I can't get it to. So I created a variable to represent the current region from cell A1, called FilterRange. The macro doesn't like that either! No matter what different variations of syntax I use, the macro won't accept it. Right now I'm getting the "Application defined or object defined error". FilterRange is dimmed as an object. Any ideas? Thanks for the help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Won't accept range name
Hi,
If you use a named range, the name must be in quotes e.g.: Dim rng as Range Set rng=Range("NamedRange") but as you define a FilterRange using Set (this is not the named range) then: FilterRange.AdvancedFilter Action:= _ ......etc should work To use a named range: RecSht.Range("FilterRange").AdvancedFilter Action:= _ If "FilterRange" is defined at workbook level, you can omit "RecSht." from the above. You are mixing Named Ranges and ranges defindd using SET so I am not sure which method you want to use. HTH "davegb" wrote: I'm trying to apply an advanced filter to a named range called "FilterRange" on the source worksheet (RecSht). RecSht.Select Range("a1").Select 'Selection.CurrentRegion.Select Set FilterRange = Selection.CurrentRegion CtyExtr.Select RecSht.Range(FilterRange).AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False I tried using a named range, but it seems that macros don't recognize a named range, at least I can't get it to. So I created a variable to represent the current region from cell A1, called FilterRange. The macro doesn't like that either! No matter what different variations of syntax I use, the macro won't accept it. Right now I'm getting the "Application defined or object defined error". FilterRange is dimmed as an object. Any ideas? Thanks for the help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Won't accept range name
Thanks for your reply. I started out trying to use a named range, but
couldn't get it to work, with or without quotes. Then I tried using it as a variable, which didn't work either. I'm open to suggestions as to which is best. One of the reasons I gave up on the named range was that I thought a named range was sheet specific, but found out when I selected FilterRange from the pull down list in the upper left corner of the spreadsheet, it selected the range from a different sheet than the one I was working on. I have a bunch of sheets which all have tables I need to filter on. So what I want it to do is get the sheet name from the original sheet called SourceSht, dimmed as object, get the data and the filter criteria from the sheet I call RecSht, and extract the data onto a sheet called CtyExtr. Both the Source sheet and the records sheet will vary, but I'll always be calling the same records sheet from the same source sheet. I.e., "A" and "A Records", "B" and "B Records", etc. The extracted data always goes to one named "County Extract" which has a variable name of CtyExtr. If this makes any sense, do you have any ideas how to make this all happen? TIA Toppers wrote: Hi, If you use a named range, the name must be in quotes e.g.: Dim rng as Range Set rng=Range("NamedRange") but as you define a FilterRange using Set (this is not the named range) then: FilterRange.AdvancedFilter Action:= _ ......etc should work To use a named range: RecSht.Range("FilterRange").AdvancedFilter Action:= _ If "FilterRange" is defined at workbook level, you can omit "RecSht." from the above. You are mixing Named Ranges and ranges defindd using SET so I am not sure which method you want to use. HTH "davegb" wrote: I'm trying to apply an advanced filter to a named range called "FilterRange" on the source worksheet (RecSht). RecSht.Select Range("a1").Select 'Selection.CurrentRegion.Select Set FilterRange = Selection.CurrentRegion CtyExtr.Select RecSht.Range(FilterRange).AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False I tried using a named range, but it seems that macros don't recognize a named range, at least I can't get it to. So I created a variable to represent the current region from cell A1, called FilterRange. The macro doesn't like that either! No matter what different variations of syntax I use, the macro won't accept it. Right now I'm getting the "Application defined or object defined error". FilterRange is dimmed as an object. Any ideas? Thanks for the help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Won't accept range name
Range names can be usefull.
but dont use too many.. and MAINTAIN them. many problems with unreadable workbooks relate to problems with names. Download NameManager from Jan Karel Pieterse (www.bmsltd.ie) and learn about global (workbook level) and local names (worksheet level) names. DONT use the same name on two levels. be aware that when you copy a sheet with GLOBAL names.. inside same workbook the copy will contain LOCAL names. (thus you have a potential problem with mixing) When you add names with VBA often the easiest way is to set the name property of a range object when assigning local names always enclose the sheetname in single quotes... Range("x1").Name = "'" & activesheet.name & "'!myrange" if excel doesn't need the quotes it will ignore them, but it wont add them if they are needed. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam davegb wrote : Thanks for your reply. I started out trying to use a named range, but couldn't get it to work, with or without quotes. Then I tried using it as a variable, which didn't work either. I'm open to suggestions as to which is best. One of the reasons I gave up on the named range was that I thought a named range was sheet specific, but found out when I selected FilterRange from the pull down list in the upper left corner of the spreadsheet, it selected the range from a different sheet than the one I was working on. I have a bunch of sheets which all have tables I need to filter on. So what I want it to do is get the sheet name from the original sheet called SourceSht, dimmed as object, get the data and the filter criteria from the sheet I call RecSht, and extract the data onto a sheet called CtyExtr. Both the Source sheet and the records sheet will vary, but I'll always be calling the same records sheet from the same source sheet. I.e., "A" and "A Records", "B" and "B Records", etc. The extracted data always goes to one named "County Extract" which has a variable name of CtyExtr. If this makes any sense, do you have any ideas how to make this all happen? TIA Toppers wrote: Hi, If you use a named range, the name must be in quotes e.g.: Dim rng as Range Set rng=Range("NamedRange") but as you define a FilterRange using Set (this is not the named range) then: FilterRange.AdvancedFilter Action:= _ ......etc should work To use a named range: RecSht.Range("FilterRange").AdvancedFilter Action:= _ If "FilterRange" is defined at workbook level, you can omit "RecSht." from the above. You are mixing Named Ranges and ranges defindd using SET so I am not sure which method you want to use. HTH "davegb" wrote: I'm trying to apply an advanced filter to a named range called "FilterRange" on the source worksheet (RecSht). RecSht.Select Range("a1").Select 'Selection.CurrentRegion.Select Set FilterRange = Selection.CurrentRegion CtyExtr.Select RecSht.Range(FilterRange).AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False I tried using a named range, but it seems that macros don't recognize a named range, at least I can't get it to. So I created a variable to represent the current region from cell A1, called FilterRange. The macro doesn't like that either! No matter what different variations of syntax I use, the macro won't accept it. Right now I'm getting the "Application defined or object defined error". FilterRange is dimmed as an object. Any ideas? Thanks for the help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Won't accept range name
Thanks for the information. It sound as if, at least in this
application, the only thing I get by using a named range is more complexity, of which I already have more than enough! Think I'll try this again without range names. keepITcool wrote: Range names can be usefull. but dont use too many.. and MAINTAIN them. many problems with unreadable workbooks relate to problems with names. Download NameManager from Jan Karel Pieterse (www.bmsltd.ie) and learn about global (workbook level) and local names (worksheet level) names. DONT use the same name on two levels. be aware that when you copy a sheet with GLOBAL names.. inside same workbook the copy will contain LOCAL names. (thus you have a potential problem with mixing) When you add names with VBA often the easiest way is to set the name property of a range object when assigning local names always enclose the sheetname in single quotes... Range("x1").Name = "'" & activesheet.name & "'!myrange" if excel doesn't need the quotes it will ignore them, but it wont add them if they are needed. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam davegb wrote : Thanks for your reply. I started out trying to use a named range, but couldn't get it to work, with or without quotes. Then I tried using it as a variable, which didn't work either. I'm open to suggestions as to which is best. One of the reasons I gave up on the named range was that I thought a named range was sheet specific, but found out when I selected FilterRange from the pull down list in the upper left corner of the spreadsheet, it selected the range from a different sheet than the one I was working on. I have a bunch of sheets which all have tables I need to filter on. So what I want it to do is get the sheet name from the original sheet called SourceSht, dimmed as object, get the data and the filter criteria from the sheet I call RecSht, and extract the data onto a sheet called CtyExtr. Both the Source sheet and the records sheet will vary, but I'll always be calling the same records sheet from the same source sheet. I.e., "A" and "A Records", "B" and "B Records", etc. The extracted data always goes to one named "County Extract" which has a variable name of CtyExtr. If this makes any sense, do you have any ideas how to make this all happen? TIA Toppers wrote: Hi, If you use a named range, the name must be in quotes e.g.: Dim rng as Range Set rng=Range("NamedRange") but as you define a FilterRange using Set (this is not the named range) then: FilterRange.AdvancedFilter Action:= _ ......etc should work To use a named range: RecSht.Range("FilterRange").AdvancedFilter Action:= _ If "FilterRange" is defined at workbook level, you can omit "RecSht." from the above. You are mixing Named Ranges and ranges defindd using SET so I am not sure which method you want to use. HTH "davegb" wrote: I'm trying to apply an advanced filter to a named range called "FilterRange" on the source worksheet (RecSht). RecSht.Select Range("a1").Select 'Selection.CurrentRegion.Select Set FilterRange = Selection.CurrentRegion CtyExtr.Select RecSht.Range(FilterRange).AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False I tried using a named range, but it seems that macros don't recognize a named range, at least I can't get it to. So I created a variable to represent the current region from cell A1, called FilterRange. The macro doesn't like that either! No matter what different variations of syntax I use, the macro won't accept it. Right now I'm getting the "Application defined or object defined error". FilterRange is dimmed as an object. Any ideas? Thanks for the help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accept or Reject a value | Excel Worksheet Functions | |||
Track/Accept Changes | Excel Worksheet Functions | |||
license box accept | Excel Discussion (Misc queries) | |||
How do i get pocket Excel to accept the normal range of formulae? | Excel Discussion (Misc queries) | |||
Won't accept sheet name | Excel Programming |