Loop and transpose sets of data
 

Hello everyone,
Im new to the list and havent done any programming in a while, but am
working on a spreadsheet for a customer and need help with something i think
is relatively simple (I just can't get it)

I have data in a set of 11 rows, which could be of varying quantites.. but
always 11 rows...

row1-11 ... text of various sorts
row12-22... text in same pattern as above..

what i need is a loop that will select the first 11 rows, and transpose and
paste into another sheet, and then select the next 11 rows and do the same,
and continue on until all data has been picked up.

Thanks in advance to anyone who can help.
Let me know if it needs clarification
Rgds
Sach

Loop and transpose sets of data
 

Sub copyandtranspose()
Dim lastrow as Long, lastCol as Long
Dim rng as Range, i as Long
With worksheets("Sheet1")
lastrow = .cells(rows.count,1).End(xlup).Row
for i = 1 to lastrow Step 11
set rng = Worksheets("Sheet2") _
.Cells(rows.count,1).End(xlup)(2)
lastcol = .cells(i,256).End(xltoLeft).Column
.cells(i,1).Resize(11,lastcol).copy
rng.PasteSpecial xlValue, Transpose:=True
Next
End With
End Sub

--
Regards,
Tom Ogilvy



"Sach" wrote in message
...
Hello everyone,
Im new to the list and havent done any programming in a while, but am
working on a spreadsheet for a customer and need help with something i
think
is relatively simple (I just can't get it)

I have data in a set of 11 rows, which could be of varying quantites.. but
always 11 rows...

row1-11 ... text of various sorts
row12-22... text in same pattern as above..

what i need is a loop that will select the first 11 rows, and transpose
and
paste into another sheet, and then select the next 11 rows and do the
same,
and continue on until all data has been picked up.

Thanks in advance to anyone who can help.
Let me know if it needs clarification
Rgds
Sach

Loop and transpose sets of data
 

Thanks very much Tom, that worked a treat. Now I need to do some LEFT/RIGHT
trimming to the data to move some of the rubbish... could someone please
assist with below?

Data is currently transposed to look like this
columns
A = 1. joe bloggs ConstructionPhone: (06) 111-1111
b = Fax: (06) 222-2222
c =999 Young Street
d= New Plum
e= PO Box 880
f = New Plum, 4615


What i need it to look like is this:
columns
a = joe bloggs Construction //remove the first 3
characters (ie
number
"1.")
b= (06) 111-1111 //remove the word Phone:
c=(06) 222-2222 //remove the word fax
d =999 Young Street
e= PO Box 880
f = New Plum //split the postcode
g =4615

I currently have it working in a rather adhoc manner into a few hidden
sheets using LEFT/RIGHT etc, but was hoping there was a way to do this in the
macro... to tidy it up...
thanks in advance
Sach




"Tom Ogilvy" wrote:

Sub copyandtranspose()
Dim lastrow as Long, lastCol as Long
Dim rng as Range, i as Long
With worksheets("Sheet1")
lastrow = .cells(rows.count,1).End(xlup).Row
for i = 1 to lastrow Step 11
set rng = Worksheets("Sheet2") _
.Cells(rows.count,1).End(xlup)(2)
lastcol = .cells(i,256).End(xltoLeft).Column
.cells(i,1).Resize(11,lastcol).copy
rng.PasteSpecial xlValue, Transpose:=True
Next
End With
End Sub

--
Regards,
Tom Ogilvy



"Sach" wrote in message
...
Hello everyone,
Im new to the list and havent done any programming in a while, but am
working on a spreadsheet for a customer and need help with something i
think
is relatively simple (I just can't get it)

I have data in a set of 11 rows, which could be of varying quantites.. but
always 11 rows...

row1-11 ... text of various sorts
row12-22... text in same pattern as above..

what i need is a loop that will select the first 11 rows, and transpose
and
paste into another sheet, and then select the next 11 rows and do the
same,
and continue on until all data has been picked up.

Thanks in advance to anyone who can help.
Let me know if it needs clarification
Rgds
Sach