Copy Inactive Worksheet using pre-defined data

    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.

    It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Love 1

    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

    I assume that you want to copy the data from A:H on the "Addresses" sheet. Is that correct? The 5 template sheets have a completely different format from the "Addresses" sheet. If you copy A:H on the "Addresses" sheet, where on the newly created sheet do you want to paste the copied data? Also, if there are going to be multiple sheets created based on column C, you will need to add perhaps the name from column A to the sheet name to prevent duplicate sheets names (which Excel does not allow). So the name of a newly created sheet may look like "John Doe-Primary Core". Please clarify in detail.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Love 1

    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.


    Thanks

    Have a look at the attached file. I have made some changes to make the coding easier. I hope that is OK with you. I have changed the template names so they match the Site Types. I have also moved the Site Name and the Site Type columns to the end. The method I am suggesting doesn't require the user to click a button. Clicking a button would be problematic because if the user clicked it when he/she was on the wrong row, the newly created sheet would not be named appropriately. The user would now enter the data in order starting in column A to column H. The macro to create the new sheet will run automatically when the selection is made in the drop down in column H. That is why I placed column H at the end. Try it out and see if it works for you.

    Files

    mike_calc.xlsm 2.6 MB – 118 Downloads

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    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.

    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.

    Replace the current macro with this one:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    I just saw your last post. Try this one:


    Quote from mike_calc

    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

    If you make a selection in a cell in column H, a new sheet is created with the name in the corresponding cell in column G. If you then change that same cell, a new sheet will not be created because a sheet with the name in the corresponding cell in column G already exists. I hope that makes sense.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    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

    Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Love 1

    You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    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?

    Quote from mike_calc

    instead of having the individual site information start on row 2 - maybe push that down to rows 6 or 7

    This won't have an effect on the macro.


    The macro references the Site Name column which is one column to the left of the Site Type: Target.Offset(, -1)

    If you move the Site Type to column K, you would have to either move the Site Name to the left of column K (column L) or change the reference (-1) in the code to the number of columns to the left of column K that the Site Name is located. Does that make sense?


    If you add additional Site Types, that will be OK as long as a corresponding sheet with the same name exists.

    Quote from mike_calc

    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?

    Could you please explain in detail what you mean by the above?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    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.

    Hi Mumps,


    I’ve attached the most recent workbook. The “site list” tab is the “addresses” tab that you helped me with last week. A new challenge came up today that I hope is possible and something you can help advise with.


    When a user enters the site abbreviation (column B on the “site list” tab) and then selects site type (column C) the new tab is created by copying one of the predefined templates and renaming it using the site abbreviation name in column B – this works perfectly, and again, thank you. Is there a way to automatically create a hyperlink in column B on the “site list” tab pointing to the newly created tab? It’s possible that as this document grows, there could be 50 to 100 plus tabs, and being able to use the “site list” tab as a way to navigate would be beneficial.


    Also, in the attached, you can see I added a column D labeled “Host Type” on the “site list” tab. Is there a way to take the contents of this cell, for each row, and populate a cell on the newly created worksheets for that site with the same values? In the sample, I have an HQ site that was created and I’d like the host type “supplier provided virtual” to show up on the HQ tab in cell B1. I’m not sure how to do that without screwing up the template that we are using to copy.


    Thanks

    Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login