blank rows between copied rows
 

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..

blank rows between copied rows
 

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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=539141

blank rows between copied rows
 

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.Co py
Worksheets("Sheet1").Paste
Destination:=Worksheets("Sheet2").Cells(iTargetRow , 1)
iSourceRow = iSourceRow + 1
iTargetRow = iTargetRow + iBlankRows
Application.CutCopyMode = False
Wend

End Sub


HTH.

--
Olly


"Arif" wrote in message
...
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..