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