Posts by mike_calc

    Thanks for the clarification – I’ll play with it a bit.

    As far as the other question around data validation. I think I figured this out. Originally column H was setup to use data validation as a way to insure users only entered in specific site type to trigger the creation of the corresponding worksheet – this list was in column N. I’ve tested and removed column N and it worked just fine.

    Question. If I add a few columns to the Addresses tab and push the "site type" column from H to something further out - say like K and instead of having the individual site information start on row 2 - maybe push that down to rows 6 or 7 - I break the macro.

    I see where I can update the code for the site type from H to K in the code "If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub".

    What isn't jumping out at me is where I would update the code for the start row is - or maybe that doesn't matter.

    Also, if I remove the cell reference of where the data validation points to for "site Type" and hard code those options directly in the data validation option box directly - will that impact the code? Or if I add additional site types down the road?

    Revised code entered – I’m only getting the error when I attempt to delete data from more than 1 row or more than 1 cell in column H.

    When I select only 1 cell in column H – works perfect. No error

    When I select cells in columns A – H of the same row and press delete I get “Run-time error ‘1004’ error which points -- shName = Target.Offset(, -1).Value in the code

    When I select cells in columns A – G (I omit H) of the same row and press delete I get no error

    When I select 2 or more cells in column H (multiple rows) I get Run-time error 13 “type-mismatch” and points to shName = Target.Offset(, -1).Value in the code

    When I select cells in columns A – G (I omit H) of multiple rows and press delete I get no error

    When I select cells in columns A – H of multiple rows and press delete I get “Run-time error ‘1004’ error which points -- shName = Target.Offset(, -1).Value in the code

    Also, I noticed that when I clear the data in a cell in column H (site type) I get a Microsoft VB error - Run-time error '1004' - Application-defined or object-defined error. I can choose End or Debug - when I choose End - it closes the error message box as if nothing happened. When I click on Debug it points me to this line in the code shName = Target.Offset(, -1).Value

    Or, if I change the site type value to something other than what it was - it does not create a new tab. This maybe OK as I can set the user expectation that once the tab is created and the site type changes - the user would need to add a new customer data row and manually delete the old one.

    Thanks - I like the workflow. Couple of things, when I enter in Site Type in column H the new tab is created/copied using the template and renamed using the data in column G (thank you) but the other tabs become hidden. Is there a way to leave the newly created tabs visible and if the user wants to hide them they can manually? And, is there a way to stay on the Addresses tab after entering in column H? I could see someone entering in many rows of site information being frustrated having to keep going back to the address tab.

    Once again, thank you. Sorry if I'm doing a terrible job explaining this. On the worksheet "addresses" when a user enters in their data on rows 7 and beyond they enter in a "site name" in column B and select what type of site it is in column C. Once they enter in all their data - maybe it just 1 site and they only populate row 7 or they have 50 sites and populate rows 7 - 57 with data. Once they do that then my thought was for them to click on the toggle button to create the new worksheets for each row they submitted data on. The new tabs will actually copy one of the template sheets (based on site site column C) and the newly created tab will be renamed using the information from column B in that row.

    Once the new tabs are created the user will be asked to populate different data on the new tabs for each site. The tabs I have in the sample spreadsheet are only samples - I plan to create the template once I get this part working.

    So in my example where a user only entered data in row 7 and selected "primary core" as the site type and named the site Customer Core - once they click on create site tabs toggle button - a new tab will be created named "customer core" and it will be a copy of the "template Core" tab. In the attachment submitted there are 11 rows populated and I would like for those 11 to each have their own tab based on site type and have it named using the name in column B.

    Also, with this being a living document it is possible for a "new" site to be added (in this case row 18) months later. I wouldn't need worksheets created for what was already existing (rows 7-17) - I would only need a tab created for the new entry (row 18) following the same methodology above.

    I appreciate your help and questions - hopefully I did a better job explaining this.


    Hi Mumps, thank you for the speedy response.

    Attached is a scrubbed version of the spreadsheet. On the first tab labeled “addresses” – this is the tab that we would have the end-user complete. In column C on this tab, they select from one of five options using a drop-down list using the data validation feature. Depending on the site type they choose – I’m looking to see if a new tab can be created using one of the 5 “template-tabs” and then naming that new tab with the value in Column B “site Name” on the addresses tab. The existing template tabs would be hidden so no end user could edit it.

    I hope that helps

    New to this level of excel and trying to see if it's possible to create a worksheet where the user enters in their address information across columns A:H and in column “C” selects a “site type” using a drop-down list containing 1 of 5 predefined options. Then based on the site-type they selected in column C, generate a new worksheet (copy) using an existing inactive/hidden worksheet (think of it as a template for the site-type they selected) and name the newly created sheet using the user data from column A of the same row. I believe a command button is necessary to kick off the process. It is possible to have multiple QTY’s of each site type requiring multiple tabs to be created with different names for each. Also, is there a way to make sure that once the tab is generated that it isn’t regenerated? This is a living document and it is possible that address locations will grow – requiring me to generate additional tabs only for those newly added sites.