Thank you again.
Copy Inactive Worksheet using pre-defined data
- mike_calc
- Thread is marked as Resolved.
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.
-
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.
-
-
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.
-
-
Try the attached file.
-
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.
-
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:
Code
Display MorePrivate Sub CommandButton1_Click() Application.ScreenUpdating = False Application.EnableEvents = False Dim numrow As String, x As Long: x = 34 numrow = Application.InputBox("How many hosts are required?", "Hosts", , , , , , 1) If IsNumeric(numrow) And numrow = 1 Then Rows("27:33").Hidden = False ElseIf IsNumeric(numrow) And numrow > 1 Then Rows("27:33").Hidden = False For i = 1 To numrow - 1 Rows("27:33").Copy Rows(x).EntireRow.Insert Shift:=xlDown Range("A" & x) = "Avaya Virtual Host " & i + 1 x = x + 7 Next i End If With Application .EnableEvents = True .CutCopyMode = False .ScreenUpdating = True End With End Sub
-
-
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?
-
Yes, the SJMC tab was created using the Site List tab and copying the Primary Core tab.
-
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
-
Try the attached file.
-
-
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
-
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.
-
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.
Code
Display MorePrivate Sub CommandButton2_Click() With Application .ScreenUpdating = False .EnableEvents = False End With Dim numrow As String, MG As Range, NR As Range, lRow As Long numrow = Application.InputBox("How many gateways are being added?", "Gateways", , , , , , 1) If numrow = "" Then Exit Sub If IsNumeric(numrow) Then Set MG = Range("A:A").Find("Media Gateways") Set NR = Range("B:B").Find("Network Region (IPC)") Rows(MG.Row).EntireRow.Hidden = False Range("A" & MG.Row + 1).Resize(, 21).Copy If Range("B" & MG.Row + 1) = "" Then Range("A" & MG.Row + 2).Resize(numrow, 21).Insert Shift:=xlDown Else lRow = Range("B5:B" & MG.Row + 2).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1 Range("A" & lRow + 1).Resize(numrow, 21).Insert Shift:=xlDown End If End If With Application .CutCopyMode = False .ScreenUpdating = True .EnableEvents = True End With End Sub
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!