Loop for copying data, then pasting in a continuous row
 

Hi All,

86.00 88.00
73.00 75.00
84.50 86.50
112.50 114.50
81.25 83.25
112.00 114.00
86.00 88.00
52.50 54.50
64.00 66.00

I have this block of data in sheet1 (i.e. A1 = 86, B1 = 88, A2 = 73, B2 = 75
etc...),

and I want to paste all this data in the first row of sheet2, so I have a
continuous line of data.

E.g 86 would go in A1, 88 in B1, 73 in C1, 75 in D1 and so on...

Is this possible to do using some sort of loop in VBA?

Many Thanks,

Bhupinder.

Loop for copying data, then pasting in a continuous row
 

Sub RearrangeData()
Dim v(1 to 18) as double
Dim i as Long, cell as Range
i = 1
for each cell in Range("A1:B9")
v(i) = cell.value
i = i + 1
Next
With Range("A1").Resize(1,18)
.Value = v
.Numberformat = "#.00"
End With
Range("A2:B9").ClearContents
End Sub

--
Regards,
Tom Ogilvy


"Bhupinder Rayat" wrote:

Hi All,

86.00 88.00
73.00 75.00
84.50 86.50
112.50 114.50
81.25 83.25
112.00 114.00
86.00 88.00
52.50 54.50
64.00 66.00

I have this block of data in sheet1 (i.e. A1 = 86, B1 = 88, A2 = 73, B2 = 75
etc...),

and I want to paste all this data in the first row of sheet2, so I have a
continuous line of data.

E.g 86 would go in A1, 88 in B1, 73 in C1, 75 in D1 and so on...

Is this possible to do using some sort of loop in VBA?

Many Thanks,

Bhupinder.

Loop for copying data, then pasting in a continuous row
 

Here is another

Sub RearrangeData1()
Dim cell as Range, i as Long
i = 0
for each cell in Range("A1:B9")
i = i + 1
cell.copy Cells(1,i)
Next
Range("A2:B9").ClearContents
End Sub

--
Regards,
Tom Ogilvy

"Bhupinder Rayat" wrote:

Hi All,

86.00 88.00
73.00 75.00
84.50 86.50
112.50 114.50
81.25 83.25
112.00 114.00
86.00 88.00
52.50 54.50
64.00 66.00

I have this block of data in sheet1 (i.e. A1 = 86, B1 = 88, A2 = 73, B2 = 75
etc...),

and I want to paste all this data in the first row of sheet2, so I have a
continuous line of data.

E.g 86 would go in A1, 88 in B1, 73 in C1, 75 in D1 and so on...

Is this possible to do using some sort of loop in VBA?

Many Thanks,

Bhupinder.

Loop for copying data, then pasting in a continuous row
 

Sorry, I missed you wanted this on another sheet

Sub RearrangeData()
Dim v(1 to 18) as double
Dim i as Long, cell as Range
Worksheets("Sheet1").Activate
i = 1
for each cell in Range("A1:B9")
v(i) = cell.value
i = i + 1
Next
With Worksheets("Sheet2").Range("A1").Resize(1,18)
.Value = v
.Numberformat = "#.00"
End With
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Sub RearrangeData()
Dim v(1 to 18) as double
Dim i as Long, cell as Range
i = 1
for each cell in Range("A1:B9")
v(i) = cell.value
i = i + 1
Next
With Range("A1").Resize(1,18)
.Value = v
.Numberformat = "#.00"
End With
Range("A2:B9").ClearContents
End Sub

--
Regards,
Tom Ogilvy


"Bhupinder Rayat" wrote:

Hi All,

86.00 88.00
73.00 75.00
84.50 86.50
112.50 114.50
81.25 83.25
112.00 114.00
86.00 88.00
52.50 54.50
64.00 66.00

I have this block of data in sheet1 (i.e. A1 = 86, B1 = 88, A2 = 73, B2 = 75
etc...),

and I want to paste all this data in the first row of sheet2, so I have a
continuous line of data.

E.g 86 would go in A1, 88 in B1, 73 in C1, 75 in D1 and so on...

Is this possible to do using some sort of loop in VBA?

Many Thanks,

Bhupinder.

Loop for copying data, then pasting in a continuous row
 

Adjusted for two sheets

Here is another

Sub RearrangeData1()
Dim cell as Range, i as Long
Dim sh1 as Worksheet, sh2 as Worksheet
set sh1 = Worksheets("sheet1")
set sh2 = Worksheets("sheet2")
i = 0
for each cell in sh1.Range("A1:B9")
i = i + 1
cell.copy sh2.Cells(1,i)
Next
End Sub

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Here is another

Sub RearrangeData1()
Dim cell as Range, i as Long
i = 0
for each cell in Range("A1:B9")
i = i + 1
cell.copy Cells(1,i)
Next
Range("A2:B9").ClearContents
End Sub

--
Regards,
Tom Ogilvy

"Bhupinder Rayat" wrote:

Hi All,

86.00 88.00
73.00 75.00
84.50 86.50
112.50 114.50
81.25 83.25
112.00 114.00
86.00 88.00
52.50 54.50
64.00 66.00

I have this block of data in sheet1 (i.e. A1 = 86, B1 = 88, A2 = 73, B2 = 75
etc...),

and I want to paste all this data in the first row of sheet2, so I have a
continuous line of data.

E.g 86 would go in A1, 88 in B1, 73 in C1, 75 in D1 and so on...

Is this possible to do using some sort of loop in VBA?

Many Thanks,

Bhupinder.

Loop for copying data, then pasting in a continuous row
 

Wow, I'm spoilt for choice.

Thank you for your help, I came up with the following...

Sub pasteValues()

Dim rowS
rowS = 5

Dim colS1, colS2
colS1 = 2
colS2 = 3

Dim rowT
rowT = 1

Dim colT1, colT2
colT1 = 1
colT2 = 2

For x = 1 To 10

For i = 1 To 9
val1 = Sheets(4).Cells(rowS, colS1)
val2 = Sheets(4).Cells(rowS, colS2)

Sheets(1).Cells(rowT, colT1) = val1
Sheets(1).Cells(rowT, colT2) = val2

rowS = rowS + 1
colT1 = colT1 + 2
colT2 = colT2 + 2
Next
rowS = 5
colS1 = colS1 + 2
colS2 = colS2 + 2
Next

End Sub

What do you think?

Thanks again.


Bhupinder

"Tom Ogilvy" wrote:

Adjusted for two sheets

Here is another

Sub RearrangeData1()
Dim cell as Range, i as Long
Dim sh1 as Worksheet, sh2 as Worksheet
set sh1 = Worksheets("sheet1")
set sh2 = Worksheets("sheet2")
i = 0
for each cell in sh1.Range("A1:B9")
i = i + 1
cell.copy sh2.Cells(1,i)
Next
End Sub

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Here is another

Sub RearrangeData1()
Dim cell as Range, i as Long
i = 0
for each cell in Range("A1:B9")
i = i + 1
cell.copy Cells(1,i)
Next
Range("A2:B9").ClearContents
End Sub

--
Regards,
Tom Ogilvy

"Bhupinder Rayat" wrote:

Hi All,

86.00 88.00
73.00 75.00
84.50 86.50
112.50 114.50
81.25 83.25
112.00 114.00
86.00 88.00
52.50 54.50
64.00 66.00

I have this block of data in sheet1 (i.e. A1 = 86, B1 = 88, A2 = 73, B2 = 75
etc...),

and I want to paste all this data in the first row of sheet2, so I have a
continuous line of data.

E.g 86 would go in A1, 88 in B1, 73 in C1, 75 in D1 and so on...

Is this possible to do using some sort of loop in VBA?

Many Thanks,

Bhupinder.