Corbechev
12-17-2019, 09:21 AM
Hello folks,
I am very new to VBA and have run into an issue that I cannot find a work around.
In my workbook there is a button on a summary page that inserts a new workbook, links it to a row on the summary page and then renames the tab based on how many previous sheets have already been inserted. When all is said and done there is also a hyperlink that links that row to the sheet in question.
My problem is this, because the sheet name could be any series of characters i wrote the code to link everything to the template copy that i create early in the code, then i rename the sheet at the end.
Everything updates to the new workbook name except the hyperlink which remains linked to the no longer relevant sheet name.
I tried to use name ranges to name a cell on each sheet before it is renamed and link the hyperlink to that, but with my coding limitations all the links just link to the last sheet that is inserted each time.
There could be upwards of 1-200 sheets on any given workbook by the end of the year so giving staff the ability to link directly to that case is important.
So, in summary, I need to be able to hyperlink a sheet reference that will update with a sheet is renamed.
Any suggestions would be welcome. We are on Excel 2016
I am very new to VBA and have run into an issue that I cannot find a work around.
In my workbook there is a button on a summary page that inserts a new workbook, links it to a row on the summary page and then renames the tab based on how many previous sheets have already been inserted. When all is said and done there is also a hyperlink that links that row to the sheet in question.
My problem is this, because the sheet name could be any series of characters i wrote the code to link everything to the template copy that i create early in the code, then i rename the sheet at the end.
Everything updates to the new workbook name except the hyperlink which remains linked to the no longer relevant sheet name.
I tried to use name ranges to name a cell on each sheet before it is renamed and link the hyperlink to that, but with my coding limitations all the links just link to the last sheet that is inserted each time.
There could be upwards of 1-200 sheets on any given workbook by the end of the year so giving staff the ability to link directly to that case is important.
So, in summary, I need to be able to hyperlink a sheet reference that will update with a sheet is renamed.
Any suggestions would be welcome. We are on Excel 2016