Converting listed data to pivot table format - repeatedly copying and pasting between two specified instances


New Member
May 21, 2019
Hi Everyone,

I am no VBA expert and appreciate any help you can provide. I would like to learn how to search for a before and after instance to copy and paste information in between those two instances. And then repeat the process. This helps to create a format usable for a pivot table.

For example if my report looks like: 1) the account information (and beginning balance) in its own row, 2) all the transactions for that account in the rows below, followed by 3) an ending balance, and then it repeats with a new account followed by those transactions and an ending balance, and so on
Row #Column AColumn BColumn CColumn D
1Account Information 1"Beginning Balance"
2Date of TransactionTransaction Info.Transaction Info.
3Date of TransactionTransaction Info.Transaction Info.
4Ending Balance thru...
5Account Information 2"Beginning Balance"
6Date of TransactionTransaction Info.Transaction Info.
7Ending Balance thru...
8Account Information 3"Beginning Balance"
9Account Information 4"Beginning Balance"
10Date of TransactionTransaction Info.Transaction Info.
11Ending Balance thru...


I would like to:
1) Search for "Beginning Balance" in column D. There is a beginning balance instance in column D for every new account.
2) Then I would like to copy a TRIMMED version (there are extra spaces that I don't want) of the column B account information into column A next to every Transaction until I reach the ending balance row.
3) I would like this to repeat for every account section.
4) Final step: delete the account information/beginning balance rows & the ending balance rows

Note: There are some instances where there is a row for an account and beginning balance but no transactions and no ending balance row so those will need to be skipped over. (Example in row 8 for Account Information 3)

Goal Table:
Row #Column AColumn BColumn CColumn D
1Account Information 1Date of TransactionTransaction Info.Transaction Info.
2Account Information 1Date of TransactionTransaction Info.Transaction Info.
3Account Information 2Date of TransactionTransaction Info.Transaction Info.
4Account Information 4Date of TransactionTransaction Info.Transaction Info.


Thank you in advance for your help!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...