Copy Inactive Worksheet using pre-defined data

  • You can say "THANK YOU" for help received by clicking the :thumbup: 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 not getting the same problem. Try the attached.

    Files

    • mike_calc8.xlsm

      (453.75 kB, downloaded 38 times, last: )

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • 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

  • 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?

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

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

  • How about:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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 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

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • 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”

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • 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”

  • I repeated the steps you described and everything worked properly.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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 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.

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

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Hopefully, we finally got it!!!!! :)

    You can say "THANK YOU" for help received by clicking the :thumbup: 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,



    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

  • In the code module for ThisWorkbook:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.