Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter & Named Range
Hi
Yet another question on my project I am afraid :( I have data in a sheet called "FULL RECORD" which I want to filter to another sheet called "FILTERED DATA" Ful Record has a named range called "PivotData" which includes the data and the header row. The workbook will be distributed amongst several users and to try to ensure that I cater for changes to the file name, and avoid conflicts with other workbooks (eg in case the user creates an old copy), I have tried to specify that the filter only looks to this workbook for the range. At the relevant point the sheet "FILTERED DATA" is active, and mycode is MySOPWorkbook = ThisWorkbook.Name Range(MySOPWorkbook + "!PivotData").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("FULL RECORD").Range("L2:M3"), CopyToRange _ :=Range("A7:BQ7"), Unique:=False NB Range("L2:M3") is a date range above the data to be filtered This works exactly as I want it to - as long as the file name has no spaces in it. Otherwise it produces an error. Can anyone suggest how I can cater for filenames with spaces? TIA LS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter & Named Range
I think I'd just use:
worksheets("full Record").range("pivotData").advancedfilter _ action:=.... But I'd fully qualify that copytorange, too: worksheets("full Record").range("pivotData").advancedfilter _ action:=xlFilterCopy, _ CriteriaRange:=Sheets("FULL RECORD").Range("L2:M3"), _ CopyToRange:=worksheets("filtered data").Range("A7:BQ7"), Unique:=False LS wrote: Hi Yet another question on my project I am afraid :( I have data in a sheet called "FULL RECORD" which I want to filter to another sheet called "FILTERED DATA" Ful Record has a named range called "PivotData" which includes the data and the header row. The workbook will be distributed amongst several users and to try to ensure that I cater for changes to the file name, and avoid conflicts with other workbooks (eg in case the user creates an old copy), I have tried to specify that the filter only looks to this workbook for the range. At the relevant point the sheet "FILTERED DATA" is active, and mycode is MySOPWorkbook = ThisWorkbook.Name Range(MySOPWorkbook + "!PivotData").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("FULL RECORD").Range("L2:M3"), CopyToRange _ :=Range("A7:BQ7"), Unique:=False NB Range("L2:M3") is a date range above the data to be filtered This works exactly as I want it to - as long as the file name has no spaces in it. Otherwise it produces an error. Can anyone suggest how I can cater for filenames with spaces? TIA LS -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter & Named Range
Thanks for the suggestion Dave, unfortunately I still get an error
at that point, caused by spaces in the filename. LS Dave Peterson wrote in message ... I think I'd just use: worksheets("full Record").range("pivotData").advancedfilter _ action:=.... But I'd fully qualify that copytorange, too: worksheets("full Record").range("pivotData").advancedfilter _ action:=xlFilterCopy, _ CriteriaRange:=Sheets("FULL RECORD").Range("L2:M3"), _ CopyToRange:=worksheets("filtered data").Range("A7:BQ7"), Unique:=False |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filter & Named Range
Even if you qualified it completely:
thisworkbook.worksheets("full Record").range("pivotData").advancedfilter _ action:=.... (I left thisworkbook off by mistake, sorry.) PivotData is on worksheet "full record", right? LS wrote: Thanks for the suggestion Dave, unfortunately I still get an error at that point, caused by spaces in the filename. LS Dave Peterson wrote in message ... I think I'd just use: worksheets("full Record").range("pivotData").advancedfilter _ action:=.... But I'd fully qualify that copytorange, too: worksheets("full Record").range("pivotData").advancedfilter _ action:=xlFilterCopy, _ CriteriaRange:=Sheets("FULL RECORD").Range("L2:M3"), _ CopyToRange:=worksheets("filtered data").Range("A7:BQ7"), Unique:=False -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hlp! Named range Advanced Filter Criterion | Excel Discussion (Misc queries) | |||
Advanced Filter - Named Ranges | Excel Worksheet Functions | |||
Advanced Filter using a range name as the criteria | Excel Worksheet Functions | |||
advanced filter a range | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |