Copy Inactive Worksheet using pre-defined data

mike_calc · Aug 14th 2020

It's adding the correct QTY but still inserting the new gateway rows before any existing gateway rows.

Mumps · Aug 14th 2020

Code

Private 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" & NR.Row - 1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
            Range("A" & lRow).Resize(numrow, 21).Insert Shift:=xlDown
        End If
    End If
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Display More

mike_calc · Aug 14th 2020

Same result. Adding any new rows before the existing

Mumps · Aug 14th 2020

I'm not getting the same problem. Try the attached.

mike_calc · Aug 14th 2020

It’s weird in that when I open the file you sent and add a gateway – it does work. However, it doesn’t work when the row being copied (the row immediately following the Media Gateway header row) is blank. When there is no value in column B of the row being copied the new rows are being inserted before the existing. See attached

Mumps · Aug 14th 2020

In the Media Gateway rows, will there always be data in column C in each row even if the corresponding cell in column B is blank?

mike_calc · Aug 14th 2020

No. If column B is blank there would be no value set in column C. I'd like to keep the row directly under Media Gateway header that we are using to copy the other rows blank and hidden - this way a user can't populate the cells and copy it over and have to go back and manually delete or change values. This way they get a clean slate to work off of..

Mumps · Aug 14th 2020

How about:

Code

Private 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 + 1).EntireRow.Hidden = False
        Range("A" & MG.Row + 1).Resize(, 21).Copy
        lRow = Range("B5:B" & NR.Row - 1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        If lRow < MG.Row Then lRow = 36
        Range("A" & lRow).Resize(numrow, 21).Insert Shift:=xlDown
        Rows(MG.Row + 1).EntireRow.Hidden = True
    End If
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Display More

mike_calc · Aug 14th 2020

I think this will work - If column B is empty then it adds the new rows above but of column B is populated it adds the new rows below. 99.99% of the time I believe column B will be populated - because it would be existing. Thank you for your help again

Mumps · Aug 14th 2020

You are very welcome.

mike_calc · Aug 14th 2020

Might have spoke too soon -

Try these scenarios –

Select “Avaya Virtual” from cell D4

Click Avaya Hosts in column E and enter 3

Select Media Gateways and enter 3 – at this point I don’t get the media gateway header row to be visible.

Select “Avaya Virtual” from cell D4

Click Avaya Hosts in column E and enter 3

Click Avaya Hosts again in column E and enter 4

Select Media Gateways and enter 3 – at this point I get a run-time error ‘91’ – Object Variable or With block variable not set – when I click on debug it points to “Rows(MG.Row + 1).EntireRow.Hidden = False”

Mumps · Aug 15th 2020

Try:

Code

Private 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 + 1).EntireRow.Hidden = False
        Range("A" & MG.Row + 1).Resize(, 21).Copy
        lRow = Range("B5:B" & NR.Row - 1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        If lRow < MG.Row Then lRow = MG.Row + 2
        Range("A" & lRow).Resize(numrow, 21).Insert Shift:=xlDown
        Rows(MG.Row + 1).EntireRow.Hidden = True
    End If
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Display More

mike_calc · Aug 17th 2020

Thanks, I copied the revised code and ran through the 2 scenarios and received the same results.

Scenario 1

Select “Avaya Virtual” from cell D4

Click Avaya Hosts in column E and enter 3

Select Media Gateways and enter 3 – at this point I don’t get the media gateway header row to be visible.

Scenario 2

Select “Avaya Virtual” from cell D4

Click Avaya Hosts in column E and enter 3

Click Avaya Hosts again in column E and enter 4

Select Media Gateways and enter 3 – at this point I get a run-time error ‘91’ – Object Variable or With block variable not set – when I click on debug it points to “Rows(MG.Row + 1).EntireRow.Hidden = False”

Mumps · Aug 17th 2020

I repeated the steps you described and everything worked properly.

mike_calc · Aug 17th 2020

I'm stumped then - I still get the same results and I even rebooted thinking maybe something just needed to be cleared - attached is the file I'm using and getting the errors. It seems when I add gateways after selecting # of Hosts more than 1 time is when the error is generated.

Mumps · Aug 18th 2020

The problem was that the media gateway header row starts out being hidden. Try:

Code

Private 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 NR = Range("B:B").Find("Network Region (IPC)")
        Rows(NR.Row - 5).Hidden = False
        Set MG = Range("A:A").Find("Media Gateways")
        Rows(MG.Row + 1).EntireRow.Hidden = False
        Range("A" & MG.Row + 1).Resize(, 21).Copy
        lRow = Range("B5:B" & NR.Row - 1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        If lRow < MG.Row Then lRow = MG.Row + 2
        Range("A" & lRow).Resize(numrow, 21).Insert Shift:=xlDown
        Rows(MG.Row + 1).EntireRow.Hidden = True
    End If
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Display More

mike_calc · Aug 18th 2020

The few tests I ran - perfect. Thanks Mumps.

Mumps · Aug 18th 2020

Hopefully, we finally got it!!!!!

mike_calc · Aug 19th 2020

Hi Mumps,

I just noticed that when we select YES in cell G4 or cell H4 – the associated tab is created and renamed appropriately. However, the template for that application is made visible. By default the template tab will be veryhidden.

Example, I select yes for Session Border Controller and a new tab is created using the session border controller template. I see the new tab with SBC appended to the end of the tab name, and I see the Session Border controller tab. See attached

Mumps · Aug 19th 2020

In the code module for ThisWorkbook:

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:=ActiveSheet
                        ActiveSheet.Name = Target.Offset(1).Value
                        .Visible = xlVeryHidden
                    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("AADS")
                    .Visible = True
                    .Copy after:=ActiveSheet
                    ActiveSheet.Name = Target.Offset(1).Value
                    .Visible = xlVeryHidden
                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