'可視セルに複数セルを貼り付け
Sub MyVisible()
Dim i As Integer
Dim myrng As Range, rng As Range
'貼り付けたい範囲を選択
Set myrng = Range(Cells(2, 3), Cells(17, 3)).SpecialCells(xlCellTypeVisible)
'貼り付け元を選択
i = 1
For Each rng In myrng
rng = Worksheets("sheet2").Cells(i, 1).Value
i = i + 1
Next
End Sub