It was overwriting because of the protection of the new sheets, my fault, when the code adds a new sheet it gets protected, but I forgot to include UserInterfaceOnly = True. That is now fixed.
I am attaching a workbook where I added 3 new sheets and added data to each. The Site Totals Table updates correctly, but, it does throw up a problem.
You will see that I added data for 2 different Rooms for Science Block Level G, they have different Difficulty Factors (1 & 3). Since there is just 1 "Difficulty" entry for each row of the Site Totals Table, the code only records the last entered, or any data update, Difficulty Factor (in this case 3 for tiling the Chemistry Lab). Not sure of the best way to overcome this issue, maybe an average of all entered Difficulty Factors for the same Block/Area & Level? Alternatively a new row for every room on each level?
I have also added code that will sort the Site Totals Table by the first column, so all Block/Areas are grouped no matter when they are entered. Also code to sort and group the Sheet Tabs.
Here is an illustration of why Excel Built-in Tables (called ListObjects by VBA) are easier to work with. This code snippet
- If IsError(Application.Match(sShtNm, .ListColumns(1).Range, 0)) Then
- If .ListRows(1).Range.Columns(1) <> "" Then .ListRows.Add , 1
- With .ListRows(.ListRows.Count).Range
- .Columns(1) = sShtNm
- .Columns(2).Resize(, UBound(y)) = y
- End With
- Else
- lRow = Application.Match(sShtNm, .ListColumns(1).Range, 0) - 1
- .ListRows(lRow).Range.Columns(2).Resize(, UBound(y)) = y
- End If
Checks if the Block/Area & Level are already in the Table, if not then a new Table Row is added (only if the Table already contains some data, if not the the first Table Row is used) and the data placed in that new Table Row, otherwise the correct Table Row gets identified and the data in that row is updated. No need to check for "the next empty row", also any formulas in the Table are automatically copied down to the newly added row.
I am attaching 2 files, one empty with the modified code, the other with 3 extra sheets with data added so you can see the issue with Difficulty Factor.