Copy_Paste_Visible_Cells_Only-CodePudding

I have been trying to Copy the Filtered data and pasting the data on filtered cell but my code is not working.

I have data in Range Sheet2.Range("O2:O10000") and i filtered this range to Sheet2.Range("O173:O2400").

I want to copy the data from filtered cells Sheet2.Range("O173:O2400") then paste this data to visible cells on same Sheet2.Range("N173:N2400")

Please note there are multiple hidden rows in this range.

Any help will be appreciated

Sub Copy_Paste__Visible_Cells_Only()

    Sheet2.Range("O173:O2400").SpecialCells(xlCellTypeVisible).Copy

    Sheet2.Range("N173:N2400").SpecialCells(xlCellTypeVisible).Paste

End Sub

CodePudding user response：

In this case, pasting won't work. As far as I know, you can't change the paste behaviour to only paste to visible cells.

When you select visible cells only, you get a collection of areas (you can think of them as a discontinuous set of ranges). Given you're just trying to move your visible data to the left, you can do it by looping through the areas and assigning their values to the same area in the previous column. Something like this:

Public Sub CopyVisible()
    Dim a As Range
    For Each a In Sheet1.Range("O4:O17").SpecialCells(xlCellTypeVisible).Areas
        a.Offset(0, -1).Value = a.Value
    Next
End Sub

The .Offset(0,-1) is signalling that you wish the values to be moved one column to the left

You can see from this example, when I filter on "a" in column O and run the macro, only the "a" values are moved to column N.

CodePudding user response：

I would use a generic sub copyVisibleCellsToOtherColumn to which you pass the source-range and the target-start range. Advantage you can re-use it for different scenarios.

Sub test_CopyVisibleCells()
Dim rgSource As Range
Set rgSource = sheet2.Range("O173:O2400")

Dim rgTarget As Range
Set rgTarget = sheet2.Range("N173:02400")

copyVisibleCells rgSource, rgTarget
End Sub

'this ist the generic sub
Public Sub copyVisibleCellsToOtherColumn(rgSource As Range, rgTarget As Range)
Dim c As Range, a As Range
For Each a In rgSource.Areas
    'this will return the visible cells within rgsource
    For Each c In a.Cells
        rgTarget.Rows(c.Row).Value = c.Value
    Next
Next
End Sub