Filtered list paste special values

[ANE0709]

Looking for a little help. other platforms and google searches say this isnt possible and offer formula work-arounds. Maybe someone one here will know something i cant seem to find. I have a filtered spreadsheet where i need to copy the filtered column X (value returned via formula) and paste special values only into same filtered column Z. i keep getting errors. excel is trying to paste the formula instead of values. it isnt lining the rows back up presumably because of the hidden filtered rows. maybe there is a VBA solution?

 

[Akuini]

@ANE0709
See if this helps:

Sub CopyVisibleToVisible1()
'use this for:
'Copy paste(value only):
'from filtered range to filtered range
'from filtered range to unfiltered range
'from unfiltered range to filtered range
'Not work on hidden column

    Dim rngA As Range
    Dim rngB As Range
    Dim r As Range
    Dim Title As String
    Dim ra As Long
    Dim rc As Long
    
    On Error GoTo skip:
    
    Title = "Copy Visible To Visible"
    Set rngA = Application.Selection
    Set rngA = Application.InputBox("Select Range to Copy then click OK:", Title, rngA.Address, Type:=8)
    
    Set rngB = Application.InputBox("Select Range to Paste (select the first cell only):", Title, Type:=8)
    Set rngB = rngB.Cells(1, 1)
    Application.ScreenUpdating = False

    ra = rngA.Rows.Count
    rc = rngA.Columns.Count
    If ra = 1 Then rngB.Resize(, rc).Value = rngA.Value: Exit Sub
    
    
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
    
    For Each r In rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.EntireRow.Hidden = False
    Next
    
    Application.GoTo rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    

Exit Sub
skip:
    If err.Number <> 424 Then
        MsgBox "Error found: " & err.Description
    End If
    
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

 

[ANE0709]

@ANE0709
See if this helps:

Sub CopyVisibleToVisible1()
'use this for:
'Copy paste(value only):
'from filtered range to filtered range
'from filtered range to unfiltered range
'from unfiltered range to filtered range
'Not work on hidden column

    Dim rngA As Range
    Dim rngB As Range
    Dim r As Range
    Dim Title As String
    Dim ra As Long
    Dim rc As Long
   
    On Error GoTo skip:
   
    Title = "Copy Visible To Visible"
    Set rngA = Application.Selection
    Set rngA = Application.InputBox("Select Range to Copy then click OK:", Title, rngA.Address, Type:=8)
   
    Set rngB = Application.InputBox("Select Range to Paste (select the first cell only):", Title, Type:=8)
    Set rngB = rngB.Cells(1, 1)
    Application.ScreenUpdating = False

    ra = rngA.Rows.Count
    rc = rngA.Columns.Count
    If ra = 1 Then rngB.Resize(, rc).Value = rngA.Value: Exit Sub
   
   
    Set rngA = rngA.Cells(1, 1).Resize(ra, 1)
   
    For Each r In rngA.SpecialCells(xlCellTypeVisible)
      rngB.Resize(1, rc).Value = r.Resize(1, rc).Value
        Do
          Set rngB = rngB.Offset(1, 0)
        Loop Until rngB.EntireRow.Hidden = False
    Next
   
    Application.GoTo rngB
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
   

Exit Sub
skip:
    If err.Number <> 424 Then
        MsgBox "Error found: " & err.Description
    End If
   
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

End Sub

This actually worked beautifully! I did make a few minor adjustments to incorporate into the rest of my existing code. This is one to remember! Thanks a bunch.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.