Try something like

Sub Test()
Set DataRange = Range(Cells(1, 1), Cells(100, 1))
For Each Cell In DataRange
For N = 1 To 20
Sheets("Sheet2").Cells(65536, 1).End(xlUp).Offset(1, 0) =
Left(Cell, Len(Cell) - 2) & Left("00", 2 - Len(N)) & N
Range(Cells(Cell.Row, 2), Cells(Cell.Row, Cells(Cell.Row,
256).End(xlToLeft).Column)).Copy
Destination:=Sheets("Sheet2").Cells(65536, 1).End(xlUp).Offset(0, 1)
Next N
Next Cell
End Sub

This will create a list on the second sheet with the 20 values stacked
beneath each other. I assume that this is what you wanted.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546400