Need help to Copy and Paste array using macro

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!

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!

Hi Patrick,
1) Just the values
2) I want to copy the data from Sheet 1 to Sheet 2.
3) Column A has multiple or range of data...eg A2:A1000 and it will be added
in future
4) Yes, some of the cells are empty

I've run the code but find error, Object required which is highlight
Do Until .Cells(rw, 1) = ""

Actually my there are two sheets, first one called 'Neighbours' which has
those data and the other is 'Sheet 1' which the data will be copied to. I
changed the name and this error showed.

thanks!


"Patrick Molloy" wrote:

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

change the code appropriately!

> With Sheet1
With Worksheets("Neighbours")

> Set source = .Range(.Cells(rw, 2), .Cells(rw, 2).End(xlToRight))
Set source = .Range(.Cells(rw, 2), .Cells(rw, 255).End(xlToLeft))


> Set target = Sheet2.Cells(rw, 2)
Set target = Worksheets("Sheet 1")..Cells(rw, 2)


"wira" wrote:

> Hi Patrick,
> 1) Just the values
> 2) I want to copy the data from Sheet 1 to Sheet 2.
> 3) Column A has multiple or range of data...eg A2:A1000 and it will be added
> in future
> 4) Yes, some of the cells are empty
>
> I've run the code but find error, Object required which is highlight
> Do Until .Cells(rw, 1) = ""
>
> Actually my there are two sheets, first one called 'Neighbours' which has
> those data and the other is 'Sheet 1' which the data will be copied to. I
> changed the name and this error showed.
>
> thanks!
>
>
> "Patrick Molloy" wrote:
>
> > 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!

Hi Patrick,

Thanks for that code, but it still copy column by column. Well that is what
i get. Ok i guess i need wanna give some decsription about this:

These are the cells that wan to be added:
A1 B1 C1 D1 E1 F1
A2 B2
A3
A4 B4 C4

In the other sheet, it'll be like this:

Col A Col B
A1 B1
A1 C1
A1 D1
A1 E1
A2 B2
A3
A4 B4
A4 C4

i think this will help to describe the whole scenario,

thanks!


"Patrick Molloy" wrote:

> change the code appropriately!
>
> > With Sheet1
> With Worksheets("Neighbours")
>
> > Set source = .Range(.Cells(rw, 2), .Cells(rw, 2).End(xlToRight))
> Set source = .Range(.Cells(rw, 2), .Cells(rw, 255).End(xlToLeft))
>
>
> > Set target = Sheet2.Cells(rw, 2)
> Set target = Worksheets("Sheet 1")..Cells(rw, 2)
>
>
> "wira" wrote:
>
> > Hi Patrick,
> > 1) Just the values
> > 2) I want to copy the data from Sheet 1 to Sheet 2.
> > 3) Column A has multiple or range of data...eg A2:A1000 and it will be added
> > in future
> > 4) Yes, some of the cells are empty
> >
> > I've run the code but find error, Object required which is highlight
> > Do Until .Cells(rw, 1) = ""
> >
> > Actually my there are two sheets, first one called 'Neighbours' which has
> > those data and the other is 'Sheet 1' which the data will be copied to. I
> > changed the name and this error showed.
> >
> > thanks!
> >
> >
> > "Patrick Molloy" wrote:
> >
> > > 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!