VBA code to make process more efficient

    Good day to you all,

    I am trying to revamp an old sheet (attached), this is what I am trying to do, and whilst I can do some of the things I need, there are a few I can not. My level of VBA is basic, and I can work out how to do a some of what I need. In the current version I have 30 odd sheets and formulas to do what I need, it is just not dynamic enough so that a user can add another X number of sheets without me spending ages re hashing all the formulas but it is clunky.


    What I need is:

    1. The user to be able to click a button and add x number of sheets, which the code will then unhide the "master" sheet and copy to the new one(s) then re-hide the sheet. Some of the cells are locked to prevent people changing, but this would just require some basic code to lock the sheet etc after it has been copied.


    2. The user will then add details to the relevant cells where it will total down the bottom using the formula, or is it easier to make VBA do the totals in the background or store them in an array for later use (point 3).


    3. These totals then need to go onto the "site totals" sheet on the next blank line. details such as the block name, concatenated with the lvl followed by the totals, I am finding it difficult to do this as there is a couple of blank cells between each total.


    I would love some input on where to start, or even some ideas of what sort of code I will need so I can continue my learning curve :).


    Thanks in advance for anyone willing to assist me :)

    This is a way of summarising several sheets of data. Also code is included to create new sheets from a template sheet.

    Roy,

    Thank you for that, That is a very good way of summing up multiple sheets that I never knew before, but I will now know and use.


    As per your first comment, I have included the actual form with some test data so you can see what data pulls from where and what I need it to do. the code on this form is mainly limited to naming the worksheet based on a cell, and the formulas are on the totals sheet.


    Adding a sheet "after" is no drama, the drama I am having is getting the totals from each sheet to populate from the totals on the other sheets, and when I add more, there is no formulas to bring them across. e.g.


    Cell row 3, gets details of block name from "O2:R2" concatenates the name if it exists from "V2:X2" on each relevant worksheet (at this stage 30). The formula in the "Totals sheet" then gets the totals from the bottom of each Worksheet D33, G33, J33 etc, this is constant across all sheets and new sheets if I were to add them.


    I was thinking (and maybe wrongly) I could add some code eventually to VBA to find the next available row "WkSht = Sheet2.range("A9999").end(xlUp).row+1" and I can probably find and modify some code to add a specific number of sheets, and add some code to populate the values of "Totals" (B3:S3) with the data from each sheet ("D33, G33, J33, ......") etc, but this is where I struggle as in my mind I would need to write a sub for each sheet unless I can automate the process depending on the number of sheets.


    I hope that adds some clarity to the issue(s), if not happy to keep trying to explain.

    Here is my take on your file.


    A major change is the use of Excel Built-in Tables, they are much easier to work with rather than manually created tables.


    When all 3 areas on the Setup sheet (School, Date & Completed by) have an entry a button will appear to add new sheet(s).


    Completing Block/Area and Level on a new sheet will create the Name for the sheet, any invalid characters will be automatically replaced with an underscore in both the Block/Area and the Sheet Name. I included the Level in the Sheet Name in case there needs to be more than 1 sheet for a Block/Area (different Levels for the same Block/Area).


    Any entry data on each new sheet will automatically update the Site Totals sheet (a new row will be automatically added for each new sheet that has data entered). Editing data on a sheet that already has data is also possible, the Site Totals sheet will automatically update with the edited data.


    Have a go with it and let me know what you think, and also let me know if anything needs changing.

    Oh my freaking god :), KJBox, thank you so much, I have been trying to work out a basic way of doing things and you just did it :). Having a look at the code, a lot simpler than I was trying to do it as well.


    Again Thanks so much.

    KJBox, just playing with the sheet and adding some data to the sheets, I have noticed that if I add say 5 sheets and then add data into the first it put that in site totals, and when I add data to the second sheet it adds to site totals, but over writes the first sheet. Do I need to add something like "WkSht = SiteTotals.range("A9999").end(xlUp).row+1" I can see the module code where you are transferring the data, but my beginner skills, are not letting me see where you are finding the next available empty cell to put the next sheet etc.

    Ok, some more follow up and testing it seems the new sheet totals do not get added to Site totals as the sheet is protected, if unprotected it adds them, the same also goes for adding rows, initially the macro was not linked to the button so I fixed that. I have been trying to run the unprotect and protect sub's when it adds rows, but it keeps giving errors so I am trying to work out why yet :)

    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

    Code
    1. If IsError(Application.Match(sShtNm, .ListColumns(1).Range, 0)) Then
    2. If .ListRows(1).Range.Columns(1) <> "" Then .ListRows.Add , 1
    3. With .ListRows(.ListRows.Count).Range
    4. .Columns(1) = sShtNm
    5. .Columns(2).Resize(, UBound(y)) = y
    6. End With
    7. Else
    8. lRow = Application.Match(sShtNm, .ListColumns(1).Range, 0) - 1
    9. .ListRows(lRow).Range.Columns(2).Resize(, UBound(y)) = y
    10. 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.

    No dramas at all with the difficulty factor, normally (our sheets) only go from 1 - 1.5 and that is per building/site, and not really an issue with this sheet as data gets transferred to a master sheet, this is just for data collection purposes, hence why the totals are on one line each.

    :)