Copy Inactive Worksheet using pre-defined data

mike_calc · Jul 31st 2020

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 · Jul 31st 2020

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.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,D4")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Select Case Target.Column
        Case Is = 1
            If Target = "N" Then
                Target.EntireRow.Hidden = True
            End If
        Case Is = 4
            If Target = "No" Then
                Rows("6:24").Hidden = True
                Rows("25:36").Hidden = False
            ElseIf Target.Value = "Yes" Then
                Rows("6:24").EntireRow.Hidden = False
                Rows("25:36").EntireRow.Hidden = True
            ElseIf Target.Value = "Mix" Then
                Rows(" 6:36").EntireRow.Hidden = False
            End If
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Display More

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?

mike_calc · Jul 31st 2020

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.

Mumps · Aug 1st 2020

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

mike_calc · Aug 4th 2020

Good morning Mumps,

Thank you - looks and works great. I'll run through some more scenarios and let you know. Thank you again.

Mumps · Aug 4th 2020

My pleasure.

mike_calc · Aug 5th 2020

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

Mumps · Aug 6th 2020

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?

mike_calc · Aug 6th 2020

Yes, the value in C2 will be the name of the worksheet

Mumps · Aug 6th 2020

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.

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name = ActiveSheet.Range("C2") Then
        If Intersect(Target, Range("G3,H3")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Select Case Target.Column
            Case Is = 8
                If Target = "Yes" Then
                    Sheets("Session Border Controller").Copy after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = Target.Offset(1).Value
                End If
            Case Is = 9
                If Target = "Yes" Then
                    Sheets("IX Messaging").Copy after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = Target.Offset(1).Value
                End If
        End Select
        Application.ScreenUpdating = True
    End If
End Sub

Display More

mike_calc · Aug 6th 2020

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.

Mumps · Aug 6th 2020

Oops!! Got the column numbers wrong. Try:

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveSheet.Name = ActiveSheet.Range("C2") Then
        If Intersect(Target, Range("G3,H3")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Select Case Target.Column
            Case Is = 7
                If Target = "Yes" Then
                    Sheets("Session Border Controller").Copy after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = Target.Offset(1).Value
                End If
            Case Is = 8
                If Target = "Yes" Then
                    Sheets("IX Messaging").Copy after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = Target.Offset(1).Value
                End If
        End Select
        Application.ScreenUpdating = True
    End If
End Sub

Display More

mike_calc · Aug 6th 2020

Perfect. Thank you very much

Mumps · Aug 6th 2020

My pleasure.

mike_calc · Aug 6th 2020

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”

Mumps · Aug 6th 2020

Try:

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If ActiveSheet.Name = ActiveSheet.Range("C2") Then
 If Intersect(Target, Range("G3,H3")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Select Case Target.Column
        Case Is = 7
            If Target = "Yes" Then
                With Sheets("Session Border Controller")
                    .Visible = True
                    .Copy after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = Target.Offset(1).Value
                    .Visible = False
                End With
            End If
        Case Is = 8
            If Target = "Yes" Then
                With Sheets("Voice Messaging")
                    .Visible = False
                    .Copy after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = Target.Offset(1).Value
                    .Visible = True
                End With
            End If
    End Select
    Application.ScreenUpdating = True
 End If
End Sub

Display More

mike_calc · Aug 6th 2020

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

mike_calc · Aug 6th 2020

I think I found it - I switched the true/false values on lines 18 and 21 - appears to be working now

mike_calc · Aug 6th 2020

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

Mumps · Aug 7th 2020

Try:

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ActSh As Worksheet
    If ActiveSheet.Name = ActiveSheet.Range("C2") Then
        If Intersect(Target, Range("G3,H3")) Is Nothing Then Exit Sub
        Set ActSh = ActiveSheet
        Application.ScreenUpdating = False
        Select Case Target.Column
            Case Is = 7
                If Target = "Yes" Then
                    With Sheets("Session Border Controller")
                        .Visible = True
                        .Copy after:=Sheets(Sheets.Count)
                        ActiveSheet.Name = Target.Offset(1).Value
                        .Visible = False
                    End With
                End If
                ActSh.Activate
                ActiveSheet.Hyperlinks.Add Anchor:=Range("G4"), Address:="", SubAddress:="'" & Target.Offset(1).Value & "'!A1", TextToDisplay:=Target.Offset(1).Value
            Case Is = 8
                If Target = "Yes" Then
                    With Sheets("Voice Messaging")
                        .Visible = True
                        .Copy after:=Sheets(Sheets.Count)
                        ActiveSheet.Name = Target.Offset(1).Value
                        .Visible = False
                    End With
                End If
                ActSh.Activate
                ActiveSheet.Hyperlinks.Add Anchor:=Range("H4"), Address:="", SubAddress:="'" & Target.Offset(1).Value & "'!A1", TextToDisplay:=Target.Offset(1).Value
        End Select
        Application.ScreenUpdating = True
    End If
End Sub

Display More

Copy Inactive Worksheet using pre-defined data

Participate now!

Share

Tags

Users Viewing This Thread