blank rows between copied rows

[Guest]

hi, if i have 10 rows of data and would like to copy them into a new sheet
with a condition of 4 blank rows of each row data, so the second will be in
the $th row, and so on, how can i do this??

exmp:

before after
12345 Andrew XXX YYY 12345 Andrew XXX YYY
12356 Andy TTT UUU -
12675 Adro NNN MMM -
13452 Budi LLL OOO -
14567 Lory MMM PPP 12356 Andy TTT UUU
etc..

Any help would be appreciated..

 

[starguy]

I dont know what you really mean. please explain exactly what you want.

 

[Olly]

Sub CopyWithRowSpaces()

Dim iSourceRow As Long, iTargetRow As Long, iBlankRows As Long

iSourceRow = 1 ' Starting row of your source dataset
iTargetRow = 1 ' Starting row of your target dataset
iBlankRows = 3 ' Set this value to the number of blank rows you'd like
in-between data rows

While Worksheets("Sheet1").Cells(iSourceRow, 1).Value <> ""
Worksheets("Sheet1").Rows(iSourceRow).EntireRow.Copy
Worksheets("Sheet1").Paste
Destination:=Worksheets("Sheet2").Cells(iTargetRow, 1)
iSourceRow = iSourceRow + 1
iTargetRow = iTargetRow + iBlankRows
Application.CutCopyMode = False
Wend

End Sub


HTH.