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

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

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

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