Insert lines at change of data and add sub totals to copy to new tab (do through a loop?)

Hi Experts,

I have attached a spreadsheet that I hope explains what I am trying to do but here is an explanation as well. I have data on one tab Columns C - M. For every change in Column C I need to sum the values in Column I. I then need to make a summary of those data points on Sheet2. I am not sure if this could all be done through a loop or if the original data needs to be reformatted. I am fine either way, I am just getting stuck in trying to get the insert and the sum to work and the copy on the inserted rows over to the summary sheet. Any help would be greatly appreciated. Please see the attached Excel file for a better, I hope, layout of what I am trying to do. Please let me know if I need to clarify what I am asking.

Thanks agiain,

theglitch

Hi Mumps1,

Thank you for the quick reply. It looks like it is almost working I am just getting a debug error on the line .Cells(.Rows.Count, fnd.Column).End(xlUp).Offset(1, 0) = tot

It is copying over the first document number from column C but nothing else. Thanks again for the help. Any additional help would be greatly appreciated.

A2 on Sheet2 is the only field getting populated.

Thanks. I am trying to run on a different spreadsheet but I am also testing on the file I uploaded and it is doing the same thing. I have version 1902 O365 would that make any difference?

The filter is only selecting the first document in the list and hiding the second one. I then get the debug screen popping up. I am not sure if that helps at all or not.

Originally Posted by theglitch

I am not sure if this could all be done through a loop
or if the original data needs to be reformatted.

Nothing to amend, just directly use the Data SubTotal Excel feature !
And if you really need a code so just with the same Range.Subtotal method in a single codeline …

Excel 2016

Replace Sheet1 with Audit Data to make it the file I am using. Everything else is the same. I just changed the sheet name in the macro to run for that sheet name instead. I can't get the Sample File to run though. Thanks again

Hi Marc L,

Thanks for the help, but I am stuck on how that moves it to Sheet2 with only the subtotals for the amounts for the changes in Column C on Sheet1. Thanks again for the help.

That seems to be working. Let me test it a bit more. Thank you very much.