VBA code to duplicate work sheet based on cell value and rename based on cell value

Hi there,

Completely stuck on this one. Hoping someone can help me on this. Not sure where to start.

In short, on "Sheet 1", I have a drop down that people can select options 1, 2, 3, 4, 5 from a drop down menu. Based on their selection I want to be able to duplicate "sheet 2" between 1 and 5 times depending on the selection from the drop down. IE: If they select "4", then sheet 2 gets duplicated 4 times. At the same time I also want each new sheet "labeled" as "1", "2", "3", "4" not the default excel names of "sheet 3, sheet 4, sheet 5" etc.


Any assistance on this would be greatly appreciated.

Thank you in advance.

Hi Mumps1,

Thanks for the reply. That is kind of my question. Sorry for not being clear, it's perhaps a little hard to explain. Maybe if I explain what I am trying to do in greater detail it might help.
Above my drop down list I have a Cell that says "Select Number of Rooms". Depending on the # of rooms selected I want sheet 2 duplicated and then then name changed for each of those sheets names changed to the "room #". So if they select "1", Sheet 2 does not copy but the sheet name is changed to "1", if they select "2" from the drop down list then I want sheet 2 copied once and then the name on sheet 2 would change to "1" and the name on sheet 3 (the duplicate) would now be "2".
Hope that helps/makes sense.
On a side note, do you know if there is a way to potentially "hire" people from this forum for specific things. IE: write some specific code based on what is needed. I have some experience with this VBA stuff but I don't know as much as many others (or so it would appear )
Thanks

Thanks, Mumps1, saw that, is there a breakdown on how points work etc. Can't seem to find it anywhere.

To you question.
Get what you are saying/asking. Didn't think about that. If they first select "2" and then select "3" afterwards then ideally an additional sheet would be added and the labels adjusted accordingly. However, if they first selected "3" and then picked "2" after that then one sheet would have to be removed and things relabeled....yikes, never thought of that of if it is even possible to do that (way beyond me for sure )

Thanks/thoughts would be appreciated (and hence the asking about the commercial stuff, would love to have someone who knows this better than me...I can usually figure it out but takes me much longer).

Hi Mumps1,

So in a nutshell and to back things up a little bit. This will probably not make much sense but I will try to explain as it is "more complex" I think.
What I am trying to accomplish is have a client select the # of rooms they want to "renovate" in their house. Could be from 1 to 15. Then in each room we have a selection of questions for them to answer so we can better provide them with a quote. Example: does the room have carpet, does it have a door etc etc.
So the way I have it set up right now is that on Sheet 1 they select the # of rooms they want to renovate. Ideally we wanted them to be able to select the room type (example: Bedroom) from the drop down menu and this would in turn "unhide" a row and place then name of that room in that now unhidden line. They then could go back to that same drop down list, select, "bathroom" and a second line would "unhide" and so on for as many rooms as they want to choose from the dropdown.
I was unable to find/make code that would allow me to select "bedroom" and then also add a row, so we split it into "number of rooms" which "unhides" the number they select. (Example: if they say they want to renovate 5 rooms, then 5 lines "unhide" and then the client selects the room type from the drop down and/or puts in their own selection).
That's part one, which, as I mentioned, I have "working", not ideal as I mentioned but it works. (two steps instead of one, pick # of rooms and then select label of rooms).
Where I am looking for help now is Part 2. Once the client has selected the # of rooms this automatically duplicates sheet 2 for each room they pick, changed the "sheet name" to the room name so when they now look at "sheet 2" it says "Bedroom" and they can answer our questions, then move on to "sheet 3" or "bathroom" and do the same thing...

Hope that make sense, very hard to explain via email.

In a nutshell, on the part that I am asking for help on (part 2). The only way I can figure out how to do it is to "have all the sheets there and then unhide" depending on how may rooms they select. IE: I currently have 15 sheets, all the same (sheets 2 through 17) and they are "hidden until they select # of sheets on Sheet 1, which then unhides the # of rooms selected. Example Code:
If Range("F30") = "1" Then
Worksheets(7).Visible = True
End If

And then to change the name of the sheet I have the Code:
If Target = Range("B34") Then
Sheets(7).Name = Range("B34").Value
End If

(Sorry I know that's not the correct way to put in code for the form but was for example only)

Again, this works, but its VERY LONG if I have to do those 2 lines of code for like 15 different scenarios (or rooms selected).


....anyway, sorry this is such a mess. Hard to explain. Hopefully it makes sense on some level.

In short, If they pick something from a drop down on sheet 1, need it to duplicate sheet 2 and change the name to sheet 2 to same name as what was selected in drop down on sheet 1. There could be 15+ selections on sheet 1 so in theory 15 "duplications" needed of sheet 2. Plus, as you suggested, also then need to have the ability to add/remove the # of duplicated sheets and change names as needed.

Thanks again, any thoughts would be appreciated.

Here it is. Had to remove a bunch of stuff but hopefully you get the jist. Thank you again for your time on this.

Yes, Sheet 2 is blank in this case, I had to remove "sensitive information from the original "sheet 2". The questions I will be asking are on "sheet 2" but couldn't share that sheet so put a blank one. The sq ft/ln ft stuff I have figured out on the back end using non-VAB stuff if the "formula bar". I just trying to figure code on how to "duplicate" sheet 2 base on number of rooms selected on sheet one (think it is Cell F30 on sheet 1). Once then have selected # of rooms, this will duplicate "sheet 2" as many times as rooms selected. IE: if they say 2 rooms, then Sheet 2 will be duplicated once (as it is already there once). If they say that there is 6 rooms then "sheet 2" gets duplicated 5 times. Then they "pick" what type of room they have (IE: bedroom/bathroom etc) (think this will be Cell B34 on sheet 1 as an example, then C34, D34 etc etc.). This will in turn change the name of the duplicated "sheet 2, sheet 3 etc. " IE: If they select 2 rooms, and one (Cell B34) is selected as "bedroom" and one (Cell C34) is selected as "bathroom" then the first "sheet 2" should be labeled "bedroom" and then sheet 2 should be duplicated as sheet 3 and and the name of "sheet 3" should be "bathroom. Hope that makes sense.

...sorry for all the notes etc. I know this is a lot. Really appreciate you taking the time to try and help with this.

Hi Mumps1,

Yes, that is correct on the 105, not likely but it is a "worst case" scenario. And yes, I will need to have different subscriptions for the "basement bathroom" verses, say, a "main floor bathroom". Haven't done that yet as wanted to get the code sorted but my thought is to simply change the item lists so that the basement bathroom will be "Base-Bathroom" and "Main-Bathroom" as so on, so the sheet labels will show up differently. Unless you have another thought/suggestion. Thanks

Thanks Mumps1,

That would be amazing, thank you for trying to assist up to this point and thank you in advance for anything you come up with.

Thanks Mumps1,

I will have a look, I'm tied up the rest of the day but will have a look first thing in the AM. Regardless, I am sure it will be much better than what I would have been able to figure out (or not figure out as the case may be). I will let you know how I make out and get back to you asap. Thank you again for all of your help. Will be in touch.

Hi Mumps1,

So had a look at what you sent me and all I can say is WOW. Amazing what you did. Thank you. Can't thank you enough. I am going to play around with it and do a couple of tweeks to make it "fit" with what I need but again, amazing. Couple of smaller things that I have noticed thus far, not sure if you can assist with or not and hopefully you can assist with, answer my question(s).

1. When I select a "room type", it creates a new sheet with the sheet labeled appropriately (just like I was looking for, again, awesome). However, when it does this it automatically "takes me" to that new sheet. Is there a way for it to "leave me" on the original sheet and just populate the new sheet without automatically switching my view to that sheet so that I can continue to add another room/sheet?
2. If I select room label "bedroom 1" from say row 34 it produces a sheet however if I go back into row 34 and change selection from "bedroom 1" to "bathroom 1", it produces a second sheet from the same row selection. Is there a way to set it up so that once a sheet is selected from a row then it will only produce "one sheet" if the room name is changed? IE: if I select "bedroom 1" and it produces the sheet and then I go back and change it to "bathroom 1", the new sheet will remain but only the name will change?
3. If I select the same room "name" more than once in the "Basement", I get an error. IE: Row 34 is "bathroom 1", produces the sheet. Row 35 is also selected as "bathroom 1", get an error. Way to fix this?
4. If I "Clear all" in the basement, can it be set up to "delete" the sheets that were initially created. IE: If in row 30 I select the # of rooms as 2, this will open up rows 34 and 35, I select those two rows as "bedroom 1" and "bathroom 1" which creates 2 new sheets. But then I change my mind and want to "Clear" rows 34 and 35 and start over by hitting "clear all" in row 30. The 2 sheets created still remain. Is there a way to "delete" those 2 created sheets?

...think that's about it for now. I know that I am asking for a lot of help and have very specific "needs" and I appreciate your assistance. Thank you again for everything. Again, WOW for what you have already done.

You wouldn't on purpose but if you did by mistake I want to prevent things from "crashing".

No, thank you

Holy Wow. That's amazing, way beyond anything I could do. Thank you.

So, couple of "bugs" if you will. (not really bugs, just not doing what I need it to). Will try and explain best I can.
1. If I select Row 34 as "bathroom", it creates a new sheet which is great, however, if I go into Row 34 and change the name it still creates an additional sheet. So now I have two sheets created from Row 34 which won't work. Need only one sheet created from one Row and if they can change the name on it then that's ideal.
2. There is a "glitch" with the "Clear all" item where you put in to delete the sheets when "clear all" is selected. Seems to only be an issue if the above process is done. IE: 2 sheets created via row 34. When you "clear all" it doesn't "clear the "second sheet created from row 34" Hope that makes sense.

Not sure if the above can be addressed/fixed. Again, absolutely stunned. Thank you.

The only other thing that I am wondering about is for the "exterior" section. Row 151. What I need to do with Rows 152 to 159 is the exact same as what you have done for the above "interior" stuff but have it "produce" new sheets as well but a "different sheet". Example: The interior stuff above that you have made work would "duplicate and name" Sheet 2 as an example (or as you have it "template"). The Exterior would do the exact same thing except instead of duplicating and naming sheet 2 (template sheet) it would duplicate and name Sheet 3 (or perhaps "template 2" if we want to stay with that labeling). Is that something that can be done?

And in conjunction with that, when a sheet is duplicated it goes to the "end" of the list of sheets. Is there a way to put them in the "middle". IE: Currently there are 5 sheets in the Excel file you have sent me. Is it possible to put the "new sheet(s)" between sheet 3 (LossOverviewData sheet) and sheet 4 (GeneralEmergData sheet) ??


Again, can't say thank you enough. I was hoping to take what you have done and add the above things myself but you are truly a master at this compared to where I am at so any additional help would be above and beyond.

Thank you

Hi Mumps1,

Again, wow, amazing. Will try and keep my response short but please know how much I appreciate everything, I'm sure you are getting sick of my "novels" when I respond We (mostly you) are so close...

"Bugs" are:

1. Still have issue with "duplicating" sheet if I go in and change my original selection. IE: if I go through and select "Bathroom 1" from the basement on row 34 it creates a new sheet labeled "basement - bathroom 1" which is perfect. However, if I "made an error" in selecting Bathroom 1 and I actually wanted Bedroom 1, it will allow me to change it in the drop down list but it will then create a second sheet based on row 34. If I go in and "clear all" it will delete only one of the sheets that I created based off of Row 34 selection. hope that makes sense. Hopefully you can try it on your end and see what I mean.
2. Getting a error with respect to " / " I am aware of this one, think it just means need to change things from "Select/Input Name" To "Select - Input Name". If that's all it is then I can address that on my end.
3. Last thing is with the "exterior" portion, you have it set up perfectly with second sheet populating etc. as I requested (again, thank you). The only thing with the "exterior" stuff is that I need it set up slightly different from the "interior" stuff. Example: when I select Row 151, this is what I need to have "trigger" a new sheet. Currently the "trigger" for the new sheet for exteriors is rows "166 and onward". In short, for exteriors then only "sheets" that I need triggered are based on rows 152 thru to 159. The "interior" of the house is set up in terms of room type (bathroom, bedroom etc) as each room could be "renovated separately". On the exterior portion we are looking for "areas/materials" not rooms per say. IE: When we are looking at the exterior of a house there is no "rooms" only areas (roof, siding, eavesthrough etc). Hope that makes sense. I think, maybe, this may be an easier fix as it's less of what you have already done?? Not sure. In short, what I need for this part is >>> I select "type of structure" from line 151, this will create a new sheet for each structure I select from line 151. At the same time, if I say select "House" the rows under the "house" (162 to 174) will also "show"/become unhidden.

Hope that helps. Thanks again.

Hi Mumps1,

Thanks for this, sorry for the delayed reply, wasn't near my computer yesterday. So everything looks amazing. Couple of "bugs" still.
1. Same as before, still have issue with "duplicating" sheet if I go in and change my original selection. IE: if I go through and select "Bathroom 1" from the basement on row 34 it creates a new sheet labeled "basement - bathroom 1" which is perfect. However, if I "made an error" in selecting Bathroom 1 and I actually wanted Bedroom 1, it will allow me to change it in the drop down list but it will then create a second sheet based on row 34. If I go in and "clear all" it will delete only one of the sheets that I created based off of Row 34 selection. hope that makes sense. Hopefully you can try it on your end and see what I mean.
2. On the "Exterior" Damage section, everything "Drops down", shows correctly except for the "Other 1" and "Other 2" in all Structures . Example: If I select "Other 1" from the drop down in row 162 then Row 173 does not "show" (same when I select "Other 2" from the drop down in row 162, Row 174 does not "show"). This is true of all other "structures" listed under Exterior Damage. As well, when I select from the drop down list in Row 235, nothing "unhides" for rows 237:247. (As an FYI, I believe that I can "fix" this "issue(s)" but wanted to make you aware of them and not "mess with" anything right now until we get "bug" in #1 figured out).

Other than those 2 things, everything looks amazing. Again, thank you for all of your assistance with this. We are so close (again, more you than me). Thank you

OMG, you did it. I can't believe it. AMAZING!!! Thank you. Thank you, Thank you. !!!!!

I hate to ask but have one last thing. If I select "Yes" in Cell "K5" or "K147" it puts me through the "motions" as needed, again unbelievably awesome what you have done/created. Everything works great. However, if the "change their mind" and there is no "interior damage or exterior damage" it "hides/resets all of the lines on that sheet however they potentially created still "show". Is there a way to "delete" any new/duplicated sheets if Cell "K5" is selected to "No", same with Cell "K147"?

Hope that makes sense.

On a side note, is there a way a can repay you/compensate you for your efforts/time etc? Not quite sure how it work within this forum, and you mentioned that things are "free", but I feel that my request(s) were potentially "larger than normal"?? Any way I can give back to you? Also, to that end, if I have further questions etc on future things is it possible to reach out to you directly as you already have an understanding of what I am looking for or do I need to go through the "regular post channels?"

Again, thank you for all you help and hopefully the last little "fix" above, you have assisted me immensely.

Sorry Should have read (see CAPS for Edit)

OMG, you did it. I can't believe it. AMAZING!!! Thank you. Thank you, Thank you. !!!!!

I hate to ask but have one last thing. If I select "Yes" in Cell "K5" or "K147" it puts me through the "motions" as needed, again unbelievably awesome what you have done/created. Everything works great. However, if the "change their mind" and there is no "interior damage or exterior damage" it "hides/resets all of the lines on that sheet however IF they potentially created a NEW SHEET IT still "showS" IN THE LIST OF SHEETS AT THE BOTTOM. Is there a way to "delete" any new/duplicated sheets if Cell "K5" is selected to "No", same with Cell "K147"?

Hope that makes sense.

On a side note, is there a way a can repay you/compensate you for your efforts/time etc? Not quite sure how it work within this forum, and you mentioned that things are "free", but I feel that my request(s) were potentially "larger than normal"?? Any way I can give back to you? Also, to that end, if I have further questions etc on future things is it possible to reach out to you directly as you already have an understanding of what I am looking for or do I need to go through the "regular post channels?"

Again, thank you for all you help and hopefully the last little "fix" above, you have assisted me immensely.

Ok, thank you Mumps1 (for the additional code part). Whenever you can, again, I appreciate it.

Understand the "volunteer basis" thing and the "new thread" thing. Was just hoping to "give back" to you in some way. I know I can say thank you by clicking on the "star" but wish I could give you a million stars and don't think that would be enough.

I will keep an eye out for the last "fix", again, thank you thank you thank you.

Sorry, yes, K15

Hi Mumps1,

So if I go in and select "bathroom1" from any room and then change it back to "Select/Input Name" there is an error that comes up saying that you can't have a "/" in the option. Not likely to have someone change it from "bathroom1" to "Select/Input Name" but want to cover my bases just in case. When with "error" came up I thought I could simply go in and change things to "Select - Input Name" so the error would go away but I tried to do that and it created more errors/things wouldn't work. Could be that I simply missed something going through the code but couldn't get it to work for some reason.

Hope that makes sense. Thanks

Oh, good question. Ideally have it "disappear" until they choose another "room name" (example bedroom 1, closet 1 etc).

Same would also be true with the Exterior "part" as well for the "House/Fence/Garage" Section where it creates a "new sheet" for those as well.

Thanks Mumps1,

Just stepping away from my computer for a few hours but will have a look as soon as I'm back. Thanks again

Hi Mumps,
Had a few minutes to jump in. Looks really good, seems to work almost perfectly. Only thing that happens now is if I select "Bathroom 1", then switch it back to "Select/Input Name" and then try to select, say, "Bedroom1" I get a "Run-time error '9' Subscript out of range" error.

Sooooo close

Please and thanks

Hi Mumps1.

This works beautifully. Thank you so much. Three quick things/questions:

1: When the message comes up stating " A sheet for that room type and area already exists.Please select a different room type." At the top of that box it says "Microsoft Excel". Is there any way to change it so that it says something else instead of "Microsoft Excel" or simply has nothing?
2: When I select a room "bathroom", it duplicated "template 1". What ever is in template one will now be in template "bathroom". The template one remains there so now I have sheet "template 1" and sheet "bathroom. Is there a way to set it up so that "template 1" becomes the first "sheet" but simply with the name changed? Example: If I select 2 rooms from the basement, it duplicates the template1 twice and produces 2 sheets, thus I have a total of 3 sheets for that selection, sheet template 1, sheet "bathroom", sheet "bedroom". I only need 2. Is there a way to have template 1 become "Bathroom", and then sheet 2 becomes "Bedroom. IE: template 1 gets name changed on first room selection, then duplicates after the first selection? Same with the exterior portions as well. If I select only "house", template 2 will not duplicate only change the sheet name to "house" but if I select "house" and "garage", then template 2 will be renamed "house" and then template 2 will duplicate and change name to "garage".
3. Lastly, Cell K15, when I select "Select One" or "no", it clears all "duplicated sheets'' which is perfect. However, when I do the same thing for Cell K147 it doesn't clear all "duplicated sheets". Both clear the rows and hides them but with K147 the sheets still show. Could we make it so the duplicated sheets are gone for K147 as it is in K15?

Hope that makes sense. Sorry for being a pain, I'm sure you are sick of dealing with me I truly do appreciate everything you have done. Some of this I don't know if I would have been able to "plan for" until I kind of played around with things a bit. Hopefully I'm not causing you too much grief.

The good news is that everything else is doing exactly what I needed it to do so that is amazing.

Hi Mumps1,

So everything works beautifully, I can't thank you enough. Thank you, Thank you, Thank you. You have made my day/week