have written following macro to paste copied data to just visible cells. It fails to work for the cells which are immediately after a hidden cell. (See the attachment, if I copy range A1:c1 from “ rowdata sheet” and then select range c1:f1 from “pastdata”sheet, when running the code, I expect c1 value of “ rowdata sheet” sheet be pasted to f1 in “pastdata” sheet but it is pasted on c6 in the “pastdata”sheet instead.
Can you guide me on this issue?

Sub pasttojustvisblecells()
Application.DisplayAlerts = False
Dim ws As Worksheet
Dim rng As Range
Dim rng1 As Range
Dim n As Integer
Dim i As Integer

Dim ws1 As Worksheet
Set ws = ActiveSheet

Set rng = Selection.SpecialCells(xlCellTypeVisible)
n = rng.SpecialCells(xlCellTypeVisible).Cells.Count
MsgBox rng.Address


Sheets.Add After:=ActiveSheet
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set rng1 = Selection
Set ws1 = ActiveSheet


For i = 1 To n


ws1.Activate
rng1.Cells(i).Select
Selection.Copy
ws.Activate
rng.Cells(i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next i
ws1.Activate
ActiveSheet.Delete
ws.Activate
Application.DisplayAlerts = True
End Sub

Attached Files

pasttojustvisblecells.xlsm (34.1 KB, 3 views)