VBA syntax issue - copying data from one location to another...

[Matty]

Hi,

Anyone any idea why this doesn't work?

    With Sheet1
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("A2:A" & LR)
        .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        .Copy Sheet2.Range("B6") [COLOR=red]PasteSpecial Paste:=xlPasteValues[/COLOR]
    End With    
    End With

I want to copy the unique values from Column A in Sheet1 to B6:B? in Sheet2, but I don't want the formatting to come across, hence xlPasteValues. But it doesn't like the xlPasteValues part.

Having Sunday evening brain issues here and can't figure out how to tweak it to deliver what I need.

Any advice welcome!

Thanks,

Matty

 

[VoG]

Try

.SpecialCells(xlCellTypeVisible).Copy
Sheet2.Range("B6").PasteSpecial Paste:=xlPasteValues

 

[Matty]

Perfect. Thanks a lot, Peter.

Matty

 

[Norie]

Matty

You could probably specify the range to copy to as one of the arguments of the advanced filter.

 

[Matty]

You could probably specify the range to copy to as one of the arguments of the advanced filter.

Hi Norie,

Could you show me an example of how this is done, please? Be good to know for future reference.

Thanks,

Matty

 

[Norie]

Matty

You need to change the Action argument and add the CopyToRange argument.

Dim LR As Long
    With Sheet1
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A1:A" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("B6"), Unique:=True
    End With

That will copy values.

However, and I just found this out, it does copy formatting.

Not all formatting though, for example it doesn't copy conditional formatting or column widths.

 

[Matty]

Thanks Norie - useful to know.

Matty