Copy Inactive Worksheet using pre-defined data

  • Thanks, I added the code and now I get a “compile error: Ambiguous name detected: Worksheet_Change” error that pops up in the VBA editor. This error pops up every time I add any text to a cell regardless of column or row I'm in.

  • That error usually means that you have two macros with the same name.

    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.

  • Once again, thank you. Still learning this side of excel so apologies if I ask obvious questions J


    It’s working now for the hyperlink but the value from the column D on the Site list tab is not populating on the newly created tab for that site (row). On the template I have an example of where it needs to go and in cell 1B on each of the templates I inserted the word “here” and when the template is copied and named the contents of cell 1B is blank.

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

  • Same result as before. The existing word of "here" in cell B1 on the newly created tab is being deleted and the cell is left blank. The contents from the "site list" tab column D for that row isn't being copied over.

  • Try the attached file. It seems to be working for me.

    Files

    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.

  • See attached file. On row 25 of the site list tab I created a site named test and the corresponding worksheet named test was created. On the newly created test worksheet the value in cell B1 is blank even though the value was set on the “site list” tab cell D25.

    Files

    • mike_calc3.xlsm

      (157.76 kB, downloaded 41 times, last: )
  • It worked for me. Look at the attached file.

    Files

    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, it works when I select the value in column D on the site list tab before I select the site type value in column C. When I complete column C before column D then I get a blank value on the new tab created.

  • Because a change in column C triggers the macro, it must be the last column populated. You could reverse columns C and D so that the Site Type is to the right of Host Type to make the flow easier and then change

    Code
    1. If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub

    to

    Code
    1. If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

    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 swapped column C and D, so now the Host Type is asked before the site type - since making that change and updating the code I don't get the Host Type updating on the new tab.


    Also, not a huge deal but if I go in to the site list tab and select a row and hit delete (clear the contents) and then enter in new text (effectively adding a new site) everything works fine but the formatting in column B changes to underline and blue (as if it were a hyperlink before it is actually a hyperlink). Is there a way to restore the formatting to it's original if the cell is empty? On the attached I used row 21 to create a site called hyperlink and then I cleared the row - typed in not a hyperlink in B21 and as you can see the formatting makes it look like a hyperlink when it is really only text. Row 22 never had any preexisting data.

  • 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, Mumps,


    It appears to be working – thank you. I moved some of the columns around to make it easier for the user to input their data. I updated the code and as far as I can tell everything is working. After reviewing the layout I also want to add the cell value in column C (sold to) on the site list tab to cell B4 on the newly created tab. I looked through the code and can’t figure out for the life of me where you specify this.

  • The "Site List" sheet in the file you attached has no 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.

  • 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 inserted the code and now when I add a new site the new tab is being hidden and the template is unhidden. Is it as simple as changing line 10 and 16 in the new code?

  • Try:

    If you don't want the new sheet hidden, delete the first ".Visible = False" line of code.

    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.

  • My pleasure. :)

    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.