Copy Inactive Worksheet using pre-defined data

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    You're 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.

    Hi Mumps,



    Quick question – is there a way to set the template tabs to be “very Hidden” and leave them like that? When I go in and set the properties for the individual tab to XlSheetVeryHidden the tab is very hidden and the user can’t unhide it, but when the user enters a new site on the site list the veryhidden tab is now only hidden. Is there a way to keep the template tabs veryhidden?



    My fear is that someone will accidently modify the template and mess everything up – if I can make it so they can’t unhide it then I think I’d be safe.

    Replace this line:

    Code
    .Visible = False

    with:

    Code
    .Visible = xlVeryHidden

    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 again for your assistance on this workbook. I’ve asked some internal users to run through a couple test scenarios to make sure everything is good. Couple things I’d like to run by you –


    • On the attached, I created a worksheet called SJMC. On this sheet, I added two hosts using the command button labeled “hosts” – this generated two hosts labeled “Avaya Virtual Host 1” and “Avaya Virtual Host 2” – which is perfect. Now, when I try to add one more host (for a total of 3) - I select the command button and enter 1 – nothing happens. This should create a 3rd host and label it according to the next available number – in this case “Avaya Virtual Host 3” – this is not happening. It’s actually creating the host and naming it “Avaya Virtual Host …. reusing numbers” and then inserting the new hosts in between the existing ones. Is there a way to keep the host numbers labeled sequentially and have them populate in order?
    • Is there a way to clear the contents of the cell when creating a host? Let’s say I’ve come back in – weeks later - and need to add another host. When the new host is created the cell contents of Host 1 is copied forcing the user to delete the contents from the new host. Is there a way to have the host created and its cell contents not be copied over?
    • Also, when I select yes for Session Border Controller or AADS in cells G3 or H3 the new tab is created – works as expected. Is there a way to have the new worksheet created next to the active tab where I selected the value. Currently the newly created tab is inserted at the end of the worksheets.

    Thank Mumps,



    I’ll say that this latest one has me stumped – LOL



    I can’t figure out where you set the row values for the Avaya Virtual Host copy/paste – I thought under the CommandButton 1 sub I see in a few spots rows 13:19 are identified and those match up with the worksheet. I ended up adding a few rows under the CPE Virtual heading – which are rows above the Avaya Virtual Host. When I change the values from 13:19 to what are now 27:33 I seem to screw up the macro. Where do I modify the rows being copied/pasted?

    The problem is that each time you modify your sheets, the macro will no longer work. I think that first you have to decide on a final version if possible and then attach a copy of the modified file.

    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.

    Ok, and that makes sense. I was hoping to find the area in the code to modify so as changes pop up I can easily address. Attached is what I anticipate being the last revision based on my internal review with the end users.



    On the “Primary Core” I have rows 6:26 that are specific to Customer Provided VMware Environment and rows 6:10 and 21:26 are visible when D4 is CPE Virtual. Rows 11:20 are hidden but the user can unhide them if they need more rows that what is visible.



    Rows 27:33 is the Avaya Virtual Host 1 and when cell D4 is Avaya Virtual, these rows are visible. This is where you modified the code earlier and I need to align the row numbers with.



    Rows 6:33 are all visible when cell D4 is set to both.



    Column A allows users to enter an N and hit enter to hide that particular row.

    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.

    Thank you again -


    All my testing is being done on the Primary Core tab


    When I select the HOST button in E3 and then select cancel, I get a Run-time error ‘13’ – Type Mismatch.



    When I select 2 hosts – I get a second host added and labeled correctly. However, the contents of the cells are being copied over. When a new host is added, only the formatting and drop-down lists should be copied.



    When I add 3 more hosts (for a total of 5) I only end up with 4 and the new hosts are being added before the existing ones and the number of the host is starting back at 1 – see attached for how the results are showing. Any new hosts should follow behind any existing



    When I add Media Gateways the new rows are being inserted before the existing rows – is there a way to have the new rows be added after the last media gateway? On the attached I added 3 media gateways – labeled these existing in column C and then added 2 more (labeled these added) – as you can see the new that were added at a later date we inserted in front of the existing.

    Do you want the macro to work the same way it did in the "SJMC" sheet in version 6 of your file?

    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.

    One more question –



    On the INR_PrimaryCoreGateway macro – the formatting is only being applied to columns A – L and not for M – P. I see in the MACRO where the columns are selected and it is only selecting A – L. I can’t figure out how I extend the selection of columns to P in the code.



    Sub INR_PrimaryCoreGateways()


    Dim MG As Range


    Set MG = Range("A:A").Find("Media Gateways")


    Rows(MG.Row).EntireRow.Hidden = False


    Range("A" & MG.Row + 1).Resize(, 12).Copy


    Range("A" & MG.Row + 2).Select


    ActiveCell.EntireRow.Insert Shift:=xlDown


    Application.CutCopyMode = False


    Application.ScreenUpdating = False


    End Sub

    I forgot to mention that you will have to replace all the CommandButton1_Click() macros with the current CommandButton1_Click() macro that is in the Primary Core module in all the other templates that will be copied

    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’m perplexed, crazy, or both – it seemed to be working when I ran through a few test scenarios but now it’s not – attached is the output from when I added hosts and gateways on the Primary Core tab



    I select one host, and rows 27:33 are visible, and row 27 is labeled and numbered correctly. Then I try to add five gateways, and instead of the new rows being added below the media gateway header row, they were inserted in the middle of the Host 1 rows (rows 30:34).



    I delete the Media Gateway rows from rows 30:34. I select Hosts and add a QTY of 3 hosts – this populates rows 27:47 correctly with the right numerical label. I then reselect Hosts and add 2 more – for a total QTY of 5. This added rows 48:61 and labeled them correctly. I now select Gateways and enter a QTY and I receive a Run-time error ‘91’ – Object variable or With block variable not set. When I click on debug it highlights this line in the INR_PrimaryCoreGateway code “ Rows(MG.Row).EntireRow.Hidden = False”. I tried changing this to True and the same error.



    I then exit out of Excel and relaunch the file – the Run-time error 91 goes away but the media gateways are being added back up in row 30

    Try the attached version. I have removed Module3 and incorporated its code into the CommandButton2_Click() macro in the Primary Core sheet.

    Files

    mike_calc8.xlsm 453.24 kB – 71 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.

    Much better - what I see now is when I add gateways (say a QTY of 5) the macro is only adding 4. Tried a few different QTY's and it is coming up 1 short each time. Also, when I add additional gateways - these are being populated before the rows where the previously added gateways are. Anytime I add gateways they should be added below the last gateway

    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.

Participate now!

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

Register Yourself Login