Copy data from report header to each transaction.

LearnVBA83

Board Regular
Joined
Dec 1, 2016
Messages
109
Office Version
  1. 365
Platform
  1. Windows
Hi VBA SuperUsers,

I've converted a txt file to excel and as many of you know the report headers are repeated in my excel document based on each change in page number on the original txt file. What I would like to do is take one piece of information from the header (the bank lockbox) and paste it beside each transaction on each page in excel. The trick is the lock box numbers do change within the title and there is no consistency. So is this possible with a macro. It would basically be saying copy Lockbox number and paste by each transaction until change in page then copy and paste the next lockbox number by each transaction.... etc. Once I figure out how to get the lockbox from the title beside each transaction, I will have the macro delete all of the report headings to give me a more manageable data set. Hopefully this will make since. It's hard to explain without being able to attach an example. For the time being below is a paste of what I have. This is only the first 3 pages out of a bunch of pages in this excel file. The bold lockboxes in column F are examples of what I want to paste beside each transaction. As you can see it changes in F57. Column K is not in the report but is an example of what I would like for the macro to do. Any help would be greatly appreciated. I've worked on this half the day and i'm getting nowhere. Thanks so much for anyone willing to help

ABCDEFGHIJK
1RUN DATE: 001/01/2017CompanyName-EBSPAGE1
2DEPOSIT DATE: 01/01/17LOCKBOX REPORT - BOX 0213OX 0213
3FROM BankName, N.A.21000089OX 0213
4OX 0213
5CHK NBR CHK ACCT NBRCHK ABA #CHECK AMTINVOICE AMTINVOICE# CUST #BATCHSEQ#SYS SEQ #OX 0213
6OX 0213
726591361297322273379$2,070.251110001OX 0213
8$2,070.25H41056318.96393E+111210001OX 0213
93272898478011322271627$5,279.021310002OX 0213
10$5,279.02H40833058.18271E+111410002OX 0213
1134893244286245122000247$1,329.361510003OX 0213
12$1,329.36H41222759.29505E+111610003OX 0213
13239791064174530122037760$2,765.421710004OX 0213
14$2,765.42H41317009.43587E+111810004OX 0213
158375202001461121144311$634.061910005OX 0213
16$634.06H4062609228187100211010005OX 0213
1778618915058831122000247$1,658.9411110006OX 0213
18$1,658.94H41277829.36098E+1111210006OX 0213
195596206489841322271724$4,472.5711310007OX 0213
20$4,472.57H38927268.9737E+1111410007OX 0213
211146020102745122243884$4,575.4311510008OX 0213
22$4,575.43H41329019.44716E+1111610008OX 0213
237401729009513122000661$4,253.5711710009OX 0213
24$4,253.57H40777318.00785E+1111810009OX 0213
2540993890785494322271627$1,095.2711910010OX 0213
26$1,095.27H40928388.46579E+1112010010OX 0213
2798881790201431122000661$1,039.2212110011OX 0213
28$1,039.22H41181579.20678E+1112210011OX 0213
292142010254912543400036$11,051.2612310012OX 0213
30$11,051.26H41221729.29321E+1112410012OX 0213
313491104857153643000096$6,476.4012510013OX 0213
32$6,476.40H41047458.9409E+1112610013OX 0213
339036151519643403224$7,066.0612710014OX 0213
34$7,066.06H41260469.34363E+1112810014OX 0213
35173971416018412243374218$27,891.0012910015OX 0213
36$27,891.00H41208079.2538E+1113010015OX 0213
3748713105974210141215032$5,442.9713110016OX 0213
38$5,442.97H41188889.21783E+1113210016OX 0213
394400104011837243374234$1,831.2413310017OX 0213
40$1,831.24H41246989.33053E+1113410017OX 0213
411258013060062043400036$3,738.5813510018OX 0213
42$3,738.58H41243539.32755E+1113610018OX 0213
43954120500957431100102$6,899.0213710019OX 0213
44$6,899.02H40856758.24415E+1113810019OX 0213
45265401.39E+1231309123$9,117.6013910020OX 0213
46$9,117.60H40876708.30057E+1114010020OX 0213
47823321026107931100102$17,902.1014110021OX 0213
48$17,902.10H41254749.34164E+1114210021OX 0213
491003729054880131301422$3,733.4514310022OX 0213
50$3,733.45H41309199.4291E+1114410022OX 0213
5137632524179175122105744$166.8014510023OX 0213
52$166.80H40884568.33813E+1114610023OX 0213
535969012534405122100024$2,458.8714710024OX 0213
54$2,458.87H405872571611100114810024OX 0213
5591292502039213122105744$2,219.4914910025OX 0213
56 RUN DATE: 001/01/2017CompanyName-EBSPAGE2OX 0213
57DEPOSIT DATE: 01/01/17LOCKBOX REPORT - BOX 4777OX 4777
58FROM BankName, N.A.21000089OX 4777
59OX 4777
60CHK NBR CHK ACCT NBRCHK ABA #CHECK AMTINVOICE AMTINVOICE# CUST #BATCHSEQ#SYS SEQ #OX 4777
61OX 4777
62$2,219.49H40811078.12324E+1115010025OX 4777
63140956521800043101000695$2,950.4515110026OX 4777
64$2,950.45H40855738.24301E+1115210026OX 4777
6578141653245043122100024$16,272.2015310027OX 4777
66$16,272.20H40733498.00255E+1115410027OX 4777
67114869196589122100024$3,541.3215510028OX 4777
68$3,541.32H39500579.49047E+1115610028OX 4777
69209865282803120122105278$1,874.4015710029OX 4777
70$1,874.40H41116209.08655E+1115810029OX 4777
71175657.0103E+11322172496$776.4215910030OX 4777
72$776.42H41453839.64787E+1116010030OX 4777
7341513739928872122100024$6,793.6016110031OX 4777
74$6,793.60H40820588.15243E+1116210031OX 4777
75116476706568539122105278$7,823.0016310032OX 4777
76$7,823.00H40798648.0802E+1116410032OX 4777
7781562527036162122105744$6,543.0616510033OX 4777
78$6,543.06H40773228.00747E+1116610033OX 4777
7946952296801331122105278$4,661.0016710034OX 4777
80$4,661.00H41033018.89121E+1116810034OX 4777
8180509346662563100277$3,781.0016910035OX 4777
82$3,781.00H4061057164516100117010035OX 4777
8315922.00004E+1262000080$1,206.7717110036OX 4777
84$1,206.77H38670609.13982E+1117210036OX 4777
8515932.00004E+1262000080$1,206.7717310037OX 4777
86$1,206.77H40549669.13982E+1117410037OX 4777
874844906264084867092022$2,733.0017510038OX 4777
88$2,733.00H40660469423605100117610038OX 4777
8910931377432433663100277$1,535.3117710039OX 4777
90$1,535.31H405745619415100117810039OX 4777
91636971700003033363102152$1,062.4117910040OX 4777
92$1,062.41H4060462142013100218010040OX 4777
932247558155110267084131$1,132.4418110041OX 4777
94$1,132.44H4059838119861100118210041OX 4777
9527862252590440122101706$4,238.0618310042OX 4777
96$4,238.06H39332559.23671E+1118410042OX 4777
97277379686590218122105278$13,509.0018510043OX 4777
98$13,509.00H41067209.00024E+1118610043OX 4777
9977072517289518122105744$922.9818710044OX 4777
100$922.98H41401189.54561E+1118810044OX 4777
1013248904156718122100024$17.2018910045OX 4777
102$17.20H41103279.06303E+1119010045OX 4777
103253511281588122100024$5,870.9019110046OX 4777
104$5,870.90H41410239.56013E+1119210046OX 4777
10526127915219863104668$3,904.7219310047OX 4777
106$3,904.72H40974728.64709E+1119410047OX 4777
10734231685550419131201328$264.6019510048OX 4777
108$264.60H4062632229353100119610048OX 4777
10910327802286743731207607$2,848.5019710049OX 4777
110$2,848.50H41121349.10087E+1119810049OX 4777
111 RUN DATE: 001/01/2017CompanyName-EBSPAGE3OX 4777
112DEPOSIT DATE: 01/01/17LOCKBOX REPORT - BOX 5128OX 5128
113FROM BankName, N.A.21000089OX 5128
114OX 5128
115CHK NBR CHK ACCT NBRCHK ABA #CHECK AMTINVOICE AMTINVOICE# CUST #BATCHSEQ#SYS SEQ #OX 5128
116OX 5128
117392291112522231372691$1,832.3719910050OX 5128
118$1,832.37H40701181.30061E+11110010050OX 5128
11923098229182216363100277$2,820.00110110051OX 5128
120$2,820.00H40662251.00374E+11110210051OX 5128
12161202.00004E+1263107513$1,266.00110310052OX 5128
122$1,266.00H405033185311041001110410052OX 5128
12311815302407511202392$2,131.12110510053OX 5128
124$2,131.12H40969238.63001E+11110610053OX 5128
125994410441339811200608$1,227.00110710054OX 5128
126$1,227.00H40612391703251001110810054OX 5128
12790039619261411200608$9,770.19110910055OX 5128
128$9,770.19H41070459.00602E+11111010055OX 5128
12935279803634224221172186$3,223.44111110056OX 5128

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
How about
Code:
Sub AddLockbox()

    With Range("K2:K" & Range("I" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=if(r[-1]c[-2]=""PAGE"",rc[-5],if(rc[-2]=""PAGE"","""",r[-1]c))"
        .Value = .Value
    End With

End Sub
And this will delete the intermediate headers as well, if needed.
Code:
Sub AddLockbox()

    Dim Ar As Areas
    Dim Rng As Range

    With Range("K2:K" & Range("I" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=if(r[-1]c[-2]=""PAGE"",rc[-5],if(rc[-2]=""PAGE"","""",r[-1]c))"
        .Value = .Value
        Set Ar = .SpecialCells(xlBlanks).Areas
    End With
    For Each Rng In Ar
        Rng.Resize(6).EntireRow.Delete
    Next Rng

End Sub
 
Last edited:
Upvote 0
Wow you made my day! I can't believe that small amount of code completed the task I wanted to see! Great job... Thank you so much!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top