please help me to refresh the macro to hyperlink the sheet name

Hi,

I have the code and it works as below.

I assigned the code to a button. Once I click on the button, it will copy the sample sheet with the sheet name that I provide, and the same name will be added to the summary page along with formula and format from the sample row. I need your help to create a hyperlink using the macro between the sheet and the sheet name updated on the summary page. Please help me where I need to add the line in the existing code below.

Also, before creating/copying the first new sheet, I will hide the sample sheet and the sample row in the summary page since no one should see it but I am facing an issue if I hide the sample sheet and the sample row in the summary page before creating/copying a new page and click on the button to create a new page, the new page and the row are hidden by themselves. please help.

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Thank you, Byte; I am getting an error as below when I click on the hyperlink.

For example, I created a sheet ABC. A hyperlink is created using your code, but if I click the hyperlink, I get an error, as given in the attached file.

Also, as I mentioned previously, before copying or creating the new sheet, I hid the sample sheet and the sample row in the summary page manually, but when I ran the code, even the new sheet and new row were hidden by themselves.

Sorry, it should be

Please Login or Register  to view this content.

Could do it like this:
NB: I think at the moment your indirect formulas don't account for a name containing a space.

Please Login or Register  to view this content.

Thank you for the quick reply. the above code is hiding ROW6. I don't want to refer to the Row6. since sometimes the table will start from different rows, so whenever the COlumn A is having the word Sample that Row should get hide.

Also, I want to hide the sheet named Sample as well.

Also, I hide Row 6 manually since it is a sample row but if you see on the screen shot even Row 7 is hidden

Sorry I thought the fact that it was hidden was the issue?

If you hide the Sample sheet it should still work in that regard.

Sorry for the confusion, I just gave an example row 6 but the sample row should hide irrespective of any rows.

Also if I hide sample sheet and create new sheet even new sheet will get hide and the sample sheet will get auto renamed to sample 2. Please help that sample sheet name should not change and the new sheet should not auto hide when it created.

Is this what you mean?

Yes, it works; the only issue is regarding Row 6. Sometimes, my table starts from Row 8 or 10 or 15 on the summary page. So, in the code, please remove the Row 6 reference. Instead, you can give a reference stating that if the A column has the word "Sample," that row should be hidden.

Hi Byte,

Please help me with the above request.

Sometimes, my table starts from Row 8 or 10 or 15 on the summary page

The sample file only had one row (Sample) so I'm not really sure what this means.

Also "Sample" is the name of the template sheet, so if you try to add another line called Sample it'll fail since that sheet name already exists.

Yes, the sample file had one row (Sample), and I needed to hide it. It will not always be in Row #6; sometimes, it will be in a different Row number. For example, on the summary page, the table header starts from Row#10. The sample Row will be on Row #11, and Row #11 should hidden. If the table header is on Row 12, and the sample row is on Row #13, it should hidden.


So whenever I create a new sheet using the button, the sheet should be created after the sample sheet. On the summary page, the new sheet name should come below the "sample" row, and the sample row should be hidden.

Maybe like this

Please Login or Register  to view this content.

Yes, it worked as per my request; one thing is that the new sheet is created before the sample sheet, and I need that new sheet to be created after the sample sheet.

Apart from that everything is working fine.

Try:
It moves the Sample sheet to the 2nd position if it isn't there already.

Please Login or Register  to view this content.

Thank you very much, it really worked well as per my request. I will mark it as solved now

Hi Byte, I am facing one issue using the above code. Please help.

When the new sheet is created, on the summary page the new sheet name is getting added outside the table. Actually, the header and the sample row are under "Table". Also, the formula and format are getting copied from the above row, I need it should be copied from the sample row instead of the above row. If I update the data manually in the above row, even those data are copied to the new row.

Please someone help me on this request.

I am facing one issue: In Column A, I need to get the newly created sheet names below the sample row, and they should hyperlink to the respective sheet, but it is not working. Also, as soon as the first sheet is created, the sample row should be hidden.

After the sample row, the next row is coming inside the table, and other rows are not getting inside the table.

Also, the formula will be copied from column B to Column W to the below row. If I add the formula in column x on the sample row in the future, even that should be copied automatically and that column should get added to inside the table.

Thank you for sharing the code will check and get back to you.

The code is not working as per my requirement. My report does not always start from Row 6 or 5; sometimes, it may go up or down. if you see the screen shot below, I created 3 sheets, only one sheet name added inside the table and other 2 sheet names are outside the table.

Hi Sudbhavani,

I rewirte the code and it is working as per my request. Attached file for your reference.

Please Login or Register  to view this content.