Copy table information and paste sorted based on specific field.
 

Hi,

I have a table in excel formated something like this

A/C Cur Amount
123 CND 100.00
153 EUR 132.22
173 US 643.99
178 EUR 124.22
142 CND 124.32
109 EUR 187.70
158 US 188.43

This is a very brief example - I have 1000's of lines like this. The
important field here is Cur (currency). There maybe up to 10 currencys in
the original. What I need to do is extract all these columns into a second
preformated sheet and separate the information out based on currency. The
second sheet should look something like this.

Co Type A/C Cur Ref Amount
SP 123 CND Batch 100.00
142 CND 124.32

SP 153 EUR Batch 132.22
178 EUR 124.22
109 EUR 187.70

SP 173 US Batch 643.99
158 US 188.43

The important thing is that for each currency the extra information is
standard and is only required on the first line.

I hope I've explained this clear enough. Any suggestions on how I would
automate this process using code would be greatly appreciated.

Paul

Copy table information and paste sorted based on specific field.
 

Sub SetUpTable()
Range("A1").CurrentRegion.Sort _
Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlYes
Columns(3).Insert
Columns(1).Insert
rw = Cells(Rows.Count, 3).End(xlUp).Row
For i = rw To 2 Step -1
If Cells(i, 3) < Cells(i - 1, 3) Then
Rows(i).Insert
Cells(i + 1, 1) = "SP"
Cells(i + 1, 4) = "Batch"
End If
Next
Cells(1, 1) = "Co Type"
Cells(1, 4) = "Ref"
Rows(2).Delete
End Sub

--
Regards,
Tom Ogilvy


"Paul" <paulm dot c @ iol dot ie wrote in message
...
Hi,

I have a table in excel formated something like this

A/C Cur Amount
123 CND 100.00
153 EUR 132.22
173 US 643.99
178 EUR 124.22
142 CND 124.32
109 EUR 187.70
158 US 188.43

This is a very brief example - I have 1000's of lines like this. The
important field here is Cur (currency). There maybe up to 10 currencys in
the original. What I need to do is extract all these columns into a second
preformated sheet and separate the information out based on currency. The
second sheet should look something like this.

Co Type A/C Cur Ref Amount
SP 123 CND Batch 100.00
142 CND 124.32

SP 153 EUR Batch 132.22
178 EUR 124.22
109 EUR 187.70

SP 173 US Batch 643.99
158 US 188.43

The important thing is that for each currency the extra information is
standard and is only required on the first line.

I hope I've explained this clear enough. Any suggestions on how I would
automate this process using code would be greatly appreciated.

Paul