Posts by mike_calc

    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).

    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”

    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.

    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, 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.

    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, Mumps,

    It appears to be working – thank you. I moved some of the columns around to make it easier for the user to input their data. I updated the code and as far as I can tell everything is working. After reviewing the layout I also want to add the cell value in column C (sold to) on the site list tab to cell B4 on the newly created tab. I looked through the code and can’t figure out for the life of me where you specify this.

    I swapped column C and D, so now the Host Type is asked before the site type - since making that change and updating the code I don't get the Host Type updating on the new tab.

    Also, not a huge deal but if I go in to the site list tab and select a row and hit delete (clear the contents) and then enter in new text (effectively adding a new site) everything works fine but the formatting in column B changes to underline and blue (as if it were a hyperlink before it is actually a hyperlink). Is there a way to restore the formatting to it's original if the cell is empty? On the attached I used row 21 to create a site called hyperlink and then I cleared the row - typed in not a hyperlink in B21 and as you can see the formatting makes it look like a hyperlink when it is really only text. Row 22 never had any preexisting data.

    Once again, thank you. Still learning this side of excel so apologies if I ask obvious questions J

    It’s working now for the hyperlink but the value from the column D on the Site list tab is not populating on the newly created tab for that site (row). On the template I have an example of where it needs to go and in cell 1B on each of the templates I inserted the word “here” and when the template is copied and named the contents of cell 1B is blank.

    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.