VBA - Duplicate Entry Created when Updating Cell Contents

[khawkins88]

This is my first post on the forum, my apologies in advanced if I do not paste the code correctly.

I am not an expert at code, nor did I write the code you see below, but I need help. Here is the workbook in a nutshell:

The user will hit a button to create a copy of the worksheet to type their profile information into. The worksheet name will automatically change based on what the user types in cell B5. Then the VBA carries certain information from the worksheet over to a summary page and hyperlinks it back to the worksheet. What we are running into is that if a user changes the information is cell B5, a duplicate entry will be created on the summary page rather than the VBA code updating the entry that is already there. I would like to change this so that the code searches the summary page for the existing entry and then updates the field that is already there instead of adding a whole new row.

Any help would be MUCH appreciated.

Please Login or Register  to view this content.

[Bernie Deitrick]

Use another cell that is filled when B5 is first changed, so that you can tell if B5 had been filled before - so choose a cell that can be filled, say B4, and after this line

Please Login or Register  to view this content.

Add this line

Please Login or Register  to view this content.

and after this line:

Please Login or Register  to view this content.

Add these 3 lines:

Please Login or Register  to view this content.

[khawkins88]

This worked!! Thank you so much!

After inputting the code, the sheet name no longer updates when B5 is changed. It remains the same as it was before. Prior to inputting the new code, the sheet name would update the same as cell B5. Thoughts on that?

[Bernie Deitrick]

Sorry - I was confused as to what you wanted. Try this version:

Please Login or Register  to view this content.

[khawkins88]

This version works fantastic for updating the sheet names and corrects the duplicate entry on the summary page, but the hyperlink breaks when the sheet name is updated.

Edited to add: In thinking more about this, it is probably because the sheet name is updating, so the original hyperlink does not have anything to refence to since the original sheet name was changed. If this is difficult to correct, we can make do with the original code additions you provided and operate understanding that any updates made to B5 will not update the sheet name. The only updates I can see occuring would be due to errors (typo, etc) on initial entry.

[Bernie Deitrick]

We can use the HyperLink function, with dynamic references to the original cell:

Please Login or Register  to view this content.

[khawkins88]

This worked - we have been testing and have run into no issues!! THANK YOU!