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" <Sach@discussions.microsoft.com> wrote in message
> news:F6097986-A485-4FC2-90C3-0ED0B56DD1A8@microsoft.com...
> > 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
>
>
>