VBA Code to copy value in a cell and paste in at the first row of the Table and delete rows below after

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi, I have to clean different excel sheets with separate tables within each sheet. I have written a vba code to extract rows between two values and paste them onto a new sheet. The issue I have now is that I want to consolidate the rows in the new sheet into a total instead of individual rows.


    From the above pic, I want the total quantity (which is 9) for the top table to replace the "1" in the first row. Thereafter, I want to delete all rows below the first row of the table till the start of the next company. After that, I want to repeat the process for Company B's table. How do I write the vba code for this? I was thinking of using find to search for "Total" and use offset after that but I'm not sure if that's possible. I have uploaded the excel workbook. You need to run the Selectbetween macro first before reaching the above step. Thank you

    What you want to do can probably be done with one macro rather than using the "Call" method. Do you want to perform the task only on the sections for the "Companies"? What about the "Individual Users" and "Full Level" sections?

    Quote from ipmh97

    I have to clean different excel sheets

    In your sample file, you have only one sheet. If you want to clean multiple sheets, please attach a revised file which contains at least 2 or 3 sheets. Also, include a sheet that shows the desired result.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login