How can i get this code to start in cell A4

[allenayres83]

I have the following code and it allows me to make a hyperlinked table of contents to my worksheets on my summary sheet. It excludes the first 6 sheets as planned but it starts the list at cell A7 on my summary sheet. I would like it to start at cell A4. Is that possible? and is it possible to add code that will add a "back to summary " hyperlink in cell A1 of each sheet that it list?

Please Login or Register  to view this content.

[kvsrinivasamurthy]

May be this

Please Login or Register  to view this content.

[allenayres83]

May be this

Please Login or Register  to view this content.

When I try this I get no result. I can see the cursor flashing but then nothing displays

[kvsrinivasamurthy]

Post a small sample file with sufficient explanation of what is the problem.

[maniacb]

For your first request, try

Please Login or Register  to view this content.

[maniacb]

For both request, try

Please Login or Register  to view this content.

[allenayres83]

For both request, try

Please Login or Register  to view this content.

So this coded seems to work but I believe my workbook is causing an issue somehow and I cant figure out why. I have any sheet past the summary sheet coded to rename the tab based on 2 cells on the sheet. The summary sheet is not set up to do that but when I run your code it seems to apply this renaming to my summary sheet. Once the tab is renamed the code stops in error becasue of the tab name change. I cant figure out why this is happening. I attached my workbook if that helps. The refresh button at the top of the summary sheet runs your code. The Summary sheet may automatically rename to Plan QTY once the sheet is downloaded and opened.

[maniacb]

The name of the sheet was changed, so I re wrote the code to use the technical name, not the sheet name. Update this code and give it a whirl

Please Login or Register  to view this content.

[allenayres83]

The name of the sheet was changed, so I re wrote the code to use the technical name, not the sheet name. Update this code and give it a whirl

Please Login or Register  to view this content.

This code works but for some reason my sheet name is still changing. This sheet name is not supposed to change. It is always supposed to be "Summary". The other sheets are the ones that are supposed to change but its like when the hyperlink is created it takes that formatting and applies it to the "Summary" sheet to change the name. I cant figure out why this is happening. Any thoughts?

[maniacb]

Try it with disabling enableevents. I didn't realize you had code on the sheet module.

Please Login or Register  to view this content.

[allenayres83]

Try it with disabling enableevents. I didn't realize you had code on the sheet module.

Please Login or Register  to view this content.

Well this works but now it is causing another problem. I have some formulas set up in the summary sheet module. These formulas only calculate if there is something in column A otherwise it remains blank. So when I add in another worksheet, which may need to be done from time to time, I have to refresh my summary sheet by running the macro again. With this code when I do that it doesnt fill in the formulas for that new sheet. Could disabling events be causing these to not calculate?

[allenayres83]

Well maybe its not the code after all. I will have to look into my summary sheet module later. Looks like my code for the formulas may be just stopping at line 67 instead of continuing on if there is something in column A

[allenayres83]

Try it with disabling enableevents. I didn't realize you had code on the sheet module.

Please Login or Register  to view this content.

I was able to play around with the code a little bit today and do some testing. The code above was causing some erratic results. If I ran the code again to refresh the list after adding additional sheets, my code that is supposed to populate formulas in columns B:G if there is data in column A didnt work. Any ideas why this is?

[maniacb]

One idea is to run a separate module to update the summary sheet after the new code is run. I created and tested the module below.

Please Login or Register  to view this content.

Note: you don't have to quote previous code already in the thread.