Excel vba: Automated Search/Copy/Paste

E

EverClear

New Member
Joined
Oct 23, 2012
Messages
32
Hi there!!

I have a “soft copy” of an 80-page report that was downloaded from an older mainframe system. The soft copy was in a .txt format, and I imported it into Excel using fixed-width delimiters for the columns. Since the report is 80-pages, it translates to almost 2,000 rows of data in Excel. An extremely truncated version of the report appears below as a sample…</SPAN>

As shown below, the companies listed on the report are grouped into “associations” that have unique association id numbers that only appear in the header of each page of the report in a single cell (example "ASSOCIATION : 1234567890").Each association’s section of the report has 2 subtotal rows called “Association Total.” </SPAN>

My dilemma - Instead of doing a manual copy/paste through 1,700+ rows of data, I need to find a way (either with an Excel formula or with vba code) to COPY each association’s unique id number from the HEADER row to the first of the 2 rows that show the “Association Total” subtotal.


</SPAN></SPAN>[TABLE="width: 879"]
<TBODY>[TR]
[TD]REPORT #</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T NAME</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]REPORT DATE</SPAN>
[/TD]
[TD="align: left"]
PAGE 1</SPAN></SPAN>

<TBODY>
</TBODY>
[/TD]
[/TR]
[TR]
[TD]JOB #</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T HEADER</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]</SPAN>






REGION : REGION NAME</SPAN></SPAN>

<TBODY>
</TBODY>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]SUMMARY</SPAN>
[/TD]
[TD]FO</SPAN>
[/TD]
[TD]R CURR</SPAN>
[/TD]
[TD]ENT MONTH</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASSOCIATION : 34567890</SPAN>
[/TD]
[TD]I want to copy
[/TD]
[TD]THIS
[/TD]
[TD]from the
[/TD]
[TD]header....
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COMPANY</SPAN>
[/TD]
[TD="colspan: 2"]COMPANY</SPAN>
[/TD]
[TD][/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]DOLLAR</SPAN>
[/TD]
[TD]NET</SPAN>
[/TD]
[/TR]
[TR]
[TD]NAME</SPAN>
[/TD]
[TD]NO.</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]INVOICED</SPAN>
[/TD]
[TD][/TD]
[TD]EXCESS</SPAN>
[/TD]
[TD]ADJUSTMENT</SPAN>
[/TD]
[TD]INVOICED</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A1</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]157</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]40,260.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]40,260.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A2</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]150</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]17,450.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]17,450.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A3</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]118</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]10,002.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]10,002.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY A4</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]107</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]20,792.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]20,792.00</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: left"]</SPAN>






COMPANY A5</SPAN></SPAN>

<TBODY>
</TBODY>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]148</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]9,427.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]9,427.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION TOTAL</SPAN>
[/TD]
[TD]to HERE!!
[/TD]
[TD="align: right"]680</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]97,931.00</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]97,931.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION TOTAL</SPAN>
[/TD]
[TD]TOTAL DEBI</SPAN>
[/TD]
[TD]TS=</SPAN>
[/TD]
[TD]121 TOT</SPAN>
[/TD]
[TD]AL CREDITS=</SPAN>
[/TD]
[TD]0 NET F</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]</SPAN>







<TBODY>
</TBODY>
[/TD]
[/TR]
[TR]
[TD]REPORT #</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T NAME</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]REPORT DATE</SPAN>
[/TD]
[TD]PAGE 2</SPAN>
[/TD]
[/TR]
[TR]
[TD]JOB #</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]REPOR</SPAN>
[/TD]
[TD]T HEADER</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]</SPAN>






REGION : REGION NAME</SPAN></SPAN>

<TBODY>
</TBODY>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]SUMMARY</SPAN>
[/TD]
[TD]FO</SPAN>
[/TD]
[TD]R CURR</SPAN>
[/TD]
[TD]ENT MONTH</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ASSOCIATION : 5678901</SPAN>
[/TD]
[TD]NEW ID#
[/TD]
[TD]I want
[/TD]
[TD]to copy
[/TD]
[TD]THIS from the
[/TD]
[TD]HEADER
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COMPANY</SPAN>
[/TD]
[TD="colspan: 2"]COMPANY</SPAN>
[/TD]
[TD][/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]DOLLAR</SPAN>
[/TD]
[TD]NET</SPAN>
[/TD]
[/TR]
[TR]
[TD]NAME</SPAN>
[/TD]
[TD]NO.</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]INVOICED</SPAN>
[/TD]
[TD][/TD]
[TD]EXCESS</SPAN>
[/TD]
[TD]ADJUSTMENT</SPAN>
[/TD]
[TD]INVOICED</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B6</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]228</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]24,558.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]24,558.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B7</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]86</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]31,257.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]31,257.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B8</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]135</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]33,922.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]33,922.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B9</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]85</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]5,807.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]5,807.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B10</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]149</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]49,828.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]49,828.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B11</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]121</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]30,245.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]30,245.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B12</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]33</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]23,988.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]23,988.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B13</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]127</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]39,978.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]39,978.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B14</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]227</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]32,909.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]32,909.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]COMPANY B15</SPAN>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]44</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]48,601.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]48,601.00</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: left"]</SPAN>






COMPANY B16</SPAN></SPAN>

<TBODY>
</TBODY>
[/TD]
[TD]###</SPAN>
[/TD]
[TD="align: right"]127</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]47,286.00</SPAN>
[/TD]
[TD="align: right"]99,999</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]47,286.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION TOTAL</SPAN>
[/TD]
[TD]...to HERE!!
[/TD]
[TD="align: right"]1362</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]368,379.00</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]368,379.00</SPAN>
[/TD]
[/TR]
[TR]
[TD]ASSOCIATION TOTAL</SPAN>
[/TD]
[TD]TOTAL DEBI</SPAN>
[/TD]
[TD]TS=</SPAN>
[/TD]
[TD]397 TOT</SPAN>
[/TD]
[TD]AL CREDITS=</SPAN>
[/TD]
[TD]0 NET F</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
It's hard to tell where everything is exactly at, but maybe this will be enough for you to change as necessary.
Code: 
Sub find_place()
    Set colA = Columns(1)
    num_associations = WorksheetFunction.CountIf(colA, "Association:")
    Range("a1").Select
    For i = 1 To num_associations
    Cells.Find(what:="Association:", after:=ActiveCell).Select
    Selection.Offset(1, 0).Copy
    Cells.Find(what:="Association Total", after:=ActiveCell).Select
    Selection.Offset(0, 1).PasteSpecial
    Next i
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel VBA inconsistent errors when trying to copy and paste objects from Excel to Word
2
Replies
18
Views
36K
Jon Peltier
J
VBA learner ITG
  • Question Question
Advice how i would tackle this using an excel formula or vba solution
Replies
1
Views
574
ImranMalek
I
E
  • Question Question
Using Loop in VBA to Delete a repeating selection of Rows
Replies
3
Views
1K
Teeroy
Teeroy
J
  • Question Question
Need help to copy certain cells based on a single cell range
Replies
4
Views
696
jkpalmer
J
P
  • Question Question
Help! Creating a unique name search using VBA
Replies
0
Views
497
ParniodAndriod
P

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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