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