Copy and paste visible cells on every other cell in a column

[geh17]

Hi guys,

Hope you can provide some help with vba. Each visible cell in a column will be copied and pasted on every other cell in a column in another sheet. For example, after filtering cells on sheet1, visible cells that will be copied are A3, A10, A14, A19, A27, etc. Each of these visible cells will be pasted one by one on every other cell in sheet2 column B which starts on B1 then B3, B5, B7, B9, and so on. Thank you so much.

 

[Fluff]

How about

Sub geh17()
   Dim Rng As Range, Cls As Range, Cl As Range
   Dim i As Long
   
   With Sheets("Sheet1")
      Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
   End With
   With Sheets("Sheet2")
      For Each Cls In Rng.Areas
         For Each Cl In Cls
            i = i + 1
            .Cells(i * 2 - 1, 1) = Cl
         Next Cl
      Next Cls
   End With
   
End Sub

change sheet names to suit

 

[geh17]

How about

Sub geh17()
   Dim Rng As Range, Cls As Range, Cl As Range
   Dim i As Long
  
   With Sheets("Sheet1")
      Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
   End With
   With Sheets("Sheet2")
      For Each Cls In Rng.Areas
         For Each Cl In Cls
            i = i + 1
            .Cells(i * 2 - 1, 1) = Cl
         Next Cl
      Next Cls
   End With
  
End Sub

change sheet names to suit

Thank you very much @Fluff!

 

[Fluff]

You're welcome & thanks for the feedback