Macro to copy, paste transpose extract at each change in contract num into new format and sheet/file

[hugobarb]

All,

I have an extract file that is in excel and lists contract numbers with a payment amount to the right. Each contract has multiple payments by period and thus there are multiple rows for each contract representing the multiple periods. The number of periods for each contract varies. The extract file contains multiple contracts. What I would like to do is have the contract number listed in one row and copy, paste, transpose the payments to the right of the contract number. The resulting file or sheet will have only one row per contract. The issue is that the code will have to recognize when the file number changes. I know I can do this manually but with 10 files and over 450 contracts this is a bit time consuming.

Attached is a sample file with two tabs titled "Extract File" and "Desired Format".
Extract File - is a sample of the main file that contains the data.
Desired Format - is a sample of what I would like the final result to be. It can be in another sheet or another file.

Thank you in advance for any help you can provide. I am very new to macros and have only used them a few times in the past.

 

[wichtel]

Hi,

Have you tried to create a pivot table for your data. It assumes you want to see the customer number in column A, you want to show the payment date as well and the whole datarange is in column A to C.

Sub CreatePivot
'taken from VBA and Macros for Microsoft Excel,2004

dim wsd as worksheet
dim ptcache as pivotcache
dim pt as pivottable
dim prange as range
fim finalrow as long
set wsd=worksheets("Extract File")
set wsd2=worksheets("Desired Format")

'Clear any prior pivot tables
for each pt in wsd.pivottables
pt.tablerange2.clear

'define input area and set pivotcache
'set cells(65536, column) to one where you have always data
finalrow=wsd.cells(65536,1).end(xlup).row
set prange=wsd.cells(1,1).resize(finalrow,3)
set ptcache=activeworkbook.pivotcaches.add(sourcetype:=xldatabase, sourcedata:=prange.address)
set pr=ptcache.createpivottable(tabledestination:=wsd2.range("A1"), tablename:="pivottable1")

with pt
.manualupdate=true
'Create frame with Customer as Row and payment date as column
.addfields rowfields:="Customer", Columnfields:="Data Payment"
'set up datafield
with pivotfields("Payment")
.orientation=xldatafield
.function=xlsum
.position=1
end with
'calculate pivottable
.manualupdate=false
.manualupdate=true
end with
end sub

Hope this helps.

 

[hugobarb]

I don't think a pivot table is what I am looking for. I wish the forum allowed me to post the sample file.