Copy Inactive Worksheet using pre-defined data

  • HI Mumps, if I need to start a new thread please let me know. Figured I start here given you are most familiar with the workbook.


    The attached workbook has multiple sheets – I’m currently focused on the “primary core” worksheet.


    The way it is designed is to hide or unhide rows based on the value in cell D4. If the value is “YES” then I need rows 6:24 to be visible and rows 25:72 to be hidden. If the value in cell D4 is “NO” then I need rows 6:24 to be hidden and rows 25:36 to be visiable. If the value is MIX then I need rows 6:36 to all be unhidden (this part isn’t working).


    Now the fun part, in column A I have it set up to allow a user to hide additional rows that are not needed by inserting a value of “N” in column A of that row and then using the command button in A5 to hide all the rows with an “N” value in column A. When I have the value in D4 set to YES, rows 6:24 are visible and then I enter “N” in column A for 1 or more rows and press the command button in A5 - the rows with an N in column A all hide – this works as expected. However, when I have the value in D4 set to NO and rows 25:36 are visible – I enter the value of N into column A for one or more rows and click the command button to hide those rows – it hides the rows as expected but also makes visible rows 6:24 that do not contain a value N in column A. Is there a way to only hide the rows that contain an N and not make visible the rows that were previously hidden?


    Also, I have a command button labeled “hosts” – I’d really like for it to work like the command button labeled “media gateways” – a user would hit the hosts button and it will ask how many hosts are being added – user enters a numeric value of 1 – 7 and it would copy/paste the rows 25:36 that number of times starting in row 37. It would also need to change the name from Host 1 to Host 2, 3, 4, etc depending on the value entered (if possible). If I do a copy/paste then it changes the row numbers of where the media gateways are (row 73) and it breaks that Macro. Is there a way to make this more dynamic so if rows are added/deleted the macro doesn’t break?


    If there is an easier way to accomplish this - I’m all ears.


    Thanks

  • Replace the current Worksheet_Change macro with this one. This should take care of entering the "N' in column A and making a selection in D4. You no longer need the button in A5. After entering the "N" just press the RETURN key.

    Also replace the current corresponding macro with this one. This keep track of the "Media Gateways" row regardless of where it moves. I'm not sure how you want the “Hosts” button to work. You want to copy rows 25:36 starting at row 37 as many times as indicated in the response. Rows 25:37 are the rows for Host 1. Will there be any other hosts below Host 1 when you click the “Hosts” button?

    You can say "THANK YOU" for help received by clicking the :thumbup: 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 inserted the code and I do like the just press enter option to hide the rows; however, I can see scenario where a user incorrectly enters an "N" in column A and has to come back and make it visible. Short of right clicking and unhiding the row they need, they may want to unhide all the rows in by reselecting YES or NO in cell D4. In this scenario, if I select YES in D4 I should see rows 6:24 and be able to delete the N in one or more rows and rehide the rows that have the N. This is what drove me to a command button.



    Following the example above I unhid rows I had previously hidden with column A and removed the letter "N" from 1 row and no error. When I tried to delete the N from 2 or more rows I received a Run-time error 13 "type mismatch" that points me to this line in the code -- If Target = "N" Then Once this error happens I can no longer hide rows using an N in column A or by using the value in D4. Also, is there a way to have it recognize both upper and lowercase N?



    Could you please clarify where and what I insert into the code for Also replace the current corresponding macro with this one. This keep track of the "Media Gateways" row regardless of where it moves. I



    And regarding the host button – because all the options under the HOST will be the same I can simply copy and paste the rows based on the value the user inputs in the hosts command button. So if they say they're adding 1 host then all I would need to do is unhide rows 25:36. But if they select 2, 3, 4, 5…etc hosts being added then I would need to unhide rows 25:36 (host 1) and copy those rows X number of times – with X being the value the user inserted with the command button. So if they inputted they were adding 3 hosts then I would unhide 25:36 (host 1) and copy/paste 2 times for host 2 and host 3. If possible, I’d like the headers for those hosts to be labeled Host 1, Host 2, Host 3 (see row 25 on the attachment I sent earlier). The copy/paste would need to happen immediately following row 36 – which pushes everything below it down and screws up my Gateway macro.

  • Try the attached file and see how it works for you.

    Files

    • mike_calc3.xlsm

      (392.27 kB, downloaded 39 times, last: )

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • My pleasure. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Hi Mumps,



    Sorry to bother you but I’ve studied your code and for the life of me can’t figure out how to copy an inactive worksheet and rename it using a cell value from an active worksheet.



    Attached is the latest workbook and on the Primary Site tab I added 2 yes or no questions in Cell G3 and H3 – by default I need them to be no but when a user changes the answer to yes have it copy an inactive worksheet that matches that template and name it using the value in G4 or H4.



    Example, I added a site using the Site List tab (row 20) and called it ADMIN – this generated a new tab called Admin. On the new Admin tab, when I change G3 to YES I’d like for the Session Border Controller tab to be copied and named using the value in G4 – in this case that is “Admin – SBC”. The same experience for H3 except instead of Session Border Controller it copies IX Messaging worksheet and names it "Admin - IX Messaging".


    Thanks

    Files

  • Cell C2 of the newly created sheet (in this case "Admin") contains the word "Admin". This corresponds to the sheet name. Will cell C2 of the newly created sheet always be the same as the sheet name of the newly created sheet?

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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, code copied as instructed and this is what I see –



    When I change the value in G3 from NO to YES – nothing happens.


    When I change the value in H3 from NO to YES – I get the new worksheet copied and renamed but it’s copying the Session Border Controller worksheet and not the IX Messaging one.

  • Oops!! Got the column numbers wrong. Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • My pleasure. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Hi Mumps,



    It’s weird in that the workflow was working and now I’m not sure what’s going on –



    I enter a new site into the Site List tab and the new tab is created and renamed – this is working as designed. See attached – row 20 I created a site and called it “Admin.”


    On the newly created “admin” tab – when I change the value in cell G3 from NO to YES the admin tab is renamed to Admin – SBC and the Session Border Controller tab that is supposed to be copied over and named Admin Building – SBC is instead hidden and named Session Border Controller (2). When I change the Voice Messaging value from No to Yes the same thing happens with the exception the name is Admin – Voice Messaging and the Voice Messaging tab that was created is hidden and named Voice Messaging (2). When I change the values in G3 and H3 to both YES then the Admin tab is renamed to Admin – Voice Messaging – SBC”

    Files

    • mike_calc5.xlsm

      (376.11 kB, downloaded 34 times, last: )
  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Here is what I get –



    When I add the site I get the new tab created and named as I would expect. On the new site tab (admin) I change cell G3 to Yes and the new session border controller tab is created and name is updated to “admin – sbc” – working as expected. However, when I change H3 to YES the following happenes 1. The active tab “admin” is renamed to admin – “voice messaging,” 2. The Voice messaging template tab is unhidden, 3. The newly created voice messaging tab is hidden and named voice messaging (2).

    Files

  • Hopefully, last questions.


    Is there a way to keep the active tab visible when a new tab is created? In the file I sent you – when I change the value on the admin tab in either cell G3 or H3 to Yes, the new tab is created and it takes me to that new tab. I’d like for the user to remain on the main tab (admin).



    Also, the number of tabs could grow to over hundred in this file, is it possible (like we did on the site list tab) to create a hyperlink to the newly created tab and insert that hyperlink in either G4 or H4? This would make it much easier to navigate

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.