Paste Values Filtered Data

[AussieVic]

Hi, is there anyway to paste values filtered data other than unfiltering data and going through the cells one by one as i dont want to paste values all cells. At the moment i am getting an error when the data is filtered and i select the cells i want to paste values.

 

[Yard]

Where do you want to paste it to?

Copying filtered data and pasting it to another location is straightforward, since Excel recognizes the filter and copies only the visible cells. When you paste it, it converts the non-contiguous range it copied to a contiguous range.

However, if you're trying to paste it back over itself then it's not so easy. In fact, I don't know a way without using VBA, but that doesn't mean one doesn't exist.

Have I understood your problem?

 

[Yard]

Pre-empting your answer to the above as "yes, I want to paste the values of a filtered list on top of themselves", try:

Sub PasteValuesFiltered()
Dim rngCopy, rngC As Range
    Set rngCopy = Range("A2:A10").SpecialCells(xlCellTypeVisible)
For Each rngC In rngCopy
    rngC.Value = rngC.Value
Next rngC
End Sub

 

[AussieVic]

Yard you rock, this is exactly what i required, i knew there must have been a VBA answer to this, a lot of people said it was not possible, you proved them wrong.

Pre-empting your answer to the above as "yes, I want to paste the values of a filtered list on top of themselves", try:

Sub PasteValuesFiltered()
Dim rngCopy, rngC As Range
    Set rngCopy = Range("A2:A10").SpecialCells(xlCellTypeVisible)
For Each rngC In rngCopy
    rngC.Value = rngC.Value
Next rngC
End Sub