Validation lost on advanced filter copy
 

I have code in a module that runs an advanced filter on a range of cells. One of
these columns has data validation linked to a list (named range). The data is
filtered no problem, but the cell with the data validation is cleared and only
the value is copied over. Is there an option that can be added to the advanced
filter code to allow the validation to transfer to the filtercopy range? Here is
the simple code I'm using.

Range("F1:F49").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"I3:I4"), CopyToRange:=Range("J4"), Unique:=True

Validation lost on advanced filter copy
 

Andy,

Advanced filter copies only value to the CopyToRange - you would need to use
code like

Range("F2").Copy
Range(Range("J4"), Range("J65536").End(xlUp)).PasteSpecial Paste:=6

The 6 is Excel's way of indicating that it should paste data validation
only: the constant isn't defined, at least for XL2000, the version I run.

HTH,
Bernie
MS Excel MVP

"Andy" wrote in message
news:oE3jc.245819$Pk3.138192@pd7tw1no...
I have code in a module that runs an advanced filter on a range of cells.
One of
these columns has data validation linked to a list (named range). The
data is
filtered no problem, but the cell with the data validation is cleared and
only
the value is copied over. Is there an option that can be added to the
advanced
filter code to allow the validation to transfer to the filtercopy range?
Here is
the simple code I'm using.

Range("F1:F49").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"I3:I4"), CopyToRange:=Range("J4"), Unique:=True