Macro to paste data from copied visible cells into adjacent visible cells only

[onefish]

Hi, I have been searching for a couple hours now and can't seem to find a suitable solution. I have a formula in column AC which constructs a concatenated and delineated list based on filtered rows (for csv import purposes). So it is dynamic based on the filters. Once I apply the filter, I need to copy visible cells in column AC and paste values (not formula, or fill left would work) into column AB in the same filtered rows. Copying is very quick in excel 2010, just select and ctrl+c and only the visible rows are selected and copied. I can't seem to find a macro that will paste to visible cells only though. I have found many macros that use an input box to select range to copy and then another to select range to paste into but this is almost as slow as just doing each row manually. Is there a simple macro that will do this? Would be great if I could just select the top visible cell to paste into and hotkey the macro to paste into visible cells down from there, like excel non filtered behaviour.

Any help is greatly appreciated.

 

[Peter_SSs]

Assuming the filter header row is row 1 (but we could make it flexible), you could try attaching this macro to a button or assign it a shortcut key combination. When run it asks for the source column and destination column split by a space. So, in your example, you would enter AC AB (upper/lower case wouldn't matter) and the visible values from column AC would be copied to the corresponding rows in column AB.

Test in a copy of your workbook.

Sub CopyFiltered()
  Dim aR As Range
  Dim Cols As String
  Dim Bits As Variant
  
  Cols = InputBox("Enter source column and destination column with a space between")
  Bits = Split(Cols)
  If UBound(Bits) = 1 Then
    Application.ScreenUpdating = False
    For Each aR In Range(Bits(0) & 2, Range(Bits(0) & Rows.Count).End(xlUp)).SpecialCells(xlVisible).Areas
      Intersect(aR.EntireRow, Columns(Bits(1))).Value = aR.Value
    Next aR
    Application.ScreenUpdating = True
  End If
End Sub

 

[onefish]

Thanks Peter, I am trying to avoid having to do the additional input if possible. I have been playing around with the below code but can't get it to work properly. It pastes values into only the filtered cells, just not the correct values. Is there a way to make it work?

Sub PasteVisible()
'
' PasteVisible Macro
'
' Keyboard Shortcut: Ctrl+m
'
    Range("AB2:AB307").SpecialCells(xlCellTypeVisible).Value = Range("AC2:AC307").SpecialCells(xlCellTypeVisible).Value
End Sub

 

[Peter_SSs]

I am trying to avoid having to do the additional input

Sorry, I thought you were looking for flexibility, at least about the destination:

Would be great if I could just select the top visible cell to paste into ...

However, if your columns and rows are fixed, as appears from that code, it is much simpler.

Sub CopyFiltered_v2()
  Dim aR As Range

  Application.ScreenUpdating = False
  For Each aR In Range("AC2:AC307").SpecialCells(xlVisible).Areas
    aR.Offset(, -1).Value = aR.Value
  Next aR
  Application.ScreenUpdating = True
End Sub

Edit: .. but you can't do it in one go like you were trying to do. Either one cell at a time or, as I have done, one contiguous set of cells (area) at a time.

 

[onefish]

Thanks again Peter. I was thinking that a "paste visible" macro would be useful in a number of applications but will settle for a worksheet specific solution for now, and your solution works perfectly

 

[Peter_SSs]

Thanks again Peter.

No problem. Glad to help.