Copying Visable cells only

[Perksy_no1]

Hi there,

I've got a macro with copies certain data from 1 sheet to the next. The problem I have is that some times there is an autofillter on the data.

I think I need to add .SpecialCells(xlCellTypeVisible) in to the code some where but im not sure where.

my current code is as follows. This is only a part of the code. It also copies other columns across to the other sheet

Sub CopyPasteMacro()
'
    With Sheets("Imprint Data")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set rngToCopy = .Range(.Range("B6:B" & LastRow), .Range("B6:B" & LastRow))
    End With
    rngToCopy.Copy
        Sheets("To Key @ TE Imprint").Range("D4").PasteSpecial Paste:=xlPasteValues
End Sub

Thanks for any help or advice in advance

Mark

 

[Perksy_no1]

Thanks for any one who looked at this but I've cracked it now thanks

Sub CopyPasteMacro()
'
        With .Range("B6:B" & LastRow)
            .SpecialCells(xlCellTypeVisible).Copy Sheets("To Key @ TE Imprint").Range("D4")
        End With
End Sub