1)do you want to copy the formual or just the values?
2) where do you want the data copied to?
3)in column A, does the cell eg A2 contain the range definition, or do we
assume that it will be that row?
4) will there be gaps in the data, eg if the data is B3:O3, will cells
possibly be empty

more details would be helpful

meantime, to copy the values:-
Option Explicit

Sub CopyData()
Dim source As Range
Dim target As Range
Dim rw As Long
With Sheet1
rw = 1
Do Until .Cells(rw, 1) = ""
Set source = .Range(.Cells(rw, 2), .Cells(rw, 2).End(xlToRight))
Set target = Sheet2.Cells(rw, 2)
target.Resize(1, source.Columns.Count).Value = source.Value

rw = rw + 1
Loop
End With
End Sub





"wira" wrote:

Hi guys,

i need help here. i have a data in Excel and i want to do copy and paste an
array using macro. There are about 1000 rows of data. Each cell, let say A1
has a group of data (B1 : O1), A2 has B2:O2 and so on. How can i copy and
paste special as transpose in diff sheets using a macro? Not all cells have a
full group of data, like A4 might has data from B4:H4. It should be like this:

A1 - B1
A1 - C1
A1 - D1
A2 - B2
A3 - B3
A3 - C3

Appreciate ur help,
thanks!