LinkBack URL
About LinkBacks
Hi All,
Is there a way to have a list of worksheets in a workbook that automatically updates when you add a new worksheet or remove a worksheet?
Regards,
JM
Hi All,
Is there a way to have a list of worksheets in a workbook that automatically updates when you add a new worksheet or remove a worksheet?
Regards,
JM
Hi Jim,
Try the macro in the attached file. You need to run the macro every time you add or delete a tab.
If you like it click on "Add Reputation"
Thanks,
aganesan99
Hi Aganesan99,
Unfortunately this is not what I am looking for, I can already insert the names using formula, so there would be no need to then have a macro to do what could be done with the formula (which would need to be updated every time a new sheet is added). What I am looking for is a way to update the list automatically when a new sheet is added - with no need for users to run anything - as this part of the report will be hidden.
I already have a formula inserts the worksheet names into a list - maybe a way for this formula to automatically update when a new worksheet has been added/removed?
Regards
JM
Hi All,
You could copy this code in sheet "LIST"
Private Sub Worksheet_Activate() Application.ScreenUpdating = False Range("A:A").ClearContents For i = 1 To Sheets.Count Cells(i, 1) = Sheets(i).Name Next i Application.ScreenUpdating = False End Sub
Hope it helps
Last edited by canapone; 11-12-2013 at 07:09 AM.
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Canapone,
Absolutely Perfect!
Thanks
Ciao,
thank you for feedback.
Hi Canapone,
Your answer was perfect, but my question was not - is there a way for the list to appear in the B column and not the A column?
Regards,
JM
Hi,
--------
Private Sub Worksheet_Activate() Application.ScreenUpdating = False Range("B:B").ClearContents For i = 1 To Sheets.Count Cells(i, 2) = Sheets(i).Name Next i Application.ScreenUpdating = False End Sub
Regards
Last edited by canapone; 11-12-2013 at 09:47 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks