Refresh Macro

[kizzie37]

I have a sheet in a workbook "Sheet Names List". Colum A has numbers 1 - 42 in rows 1 - 42. Column B has the following formula =IFERROR(INDEX(ListSheets,A1),""). I just need a macro that will "Refresh" this index list.

the reason for the need is because whenever I copy an exiting worksheet (as a template) and rename it, the "Sheet Names List" does not update with the new sheet name and continues to reference the old sheet name unless I manually refresh the formula. I want this to be automated.

Please can you assist

 

[dreid1011]

I have a sheet in a workbook "Sheet Names List". Colum A has numbers 1 - 42 in rows 1 - 42. Column B has the following formula =IFERROR(INDEX(ListSheets,A1),""). I just need a macro that will "Refresh" this index list.

the reason for the need is because whenever I copy an exiting worksheet (as a template) and rename it, the "Sheet Names List" does not update with the new sheet name and continues to reference the old sheet name unless I manually refresh the formula. I want this to be automated.

Please can you assist

Maybe a Worksheet_Change event can handle this. I have not tested this.

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Worksheets("Sheet Names List").Range("B1:B42").Calculate
End Sub

 

[kizzie37]

Maybe a Worksheet_Change event can handle this. I have not tested this.

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Worksheets("Sheet Names List").Range("B1:B42").Calculate
End Sub

Thanks dReid, Unfortunately this does not work. As soon as I copy an existing sheet it notes the name if the sheet "as copied" but wont log the name change to the sheet, which i need for a data validation in another tab. Is there another way to achieve this and keep the list updated?

 

[dreid1011]

Thanks dReid, Unfortunately this does not work. As soon as I copy an existing sheet it notes the name if the sheet "as copied" but wont log the name change to the sheet, which i need for a data validation in another tab. Is there another way to achieve this and keep the list updated?

While looking for a solution, I came across this also, but I haven't tested it either. I was hoping the Worksheet_Change would have worked too. I may be able to look into this more later though.

Rename Worksheet Event in Excel

What is the best way to get some VBA code to run when a excel sheet is renamed?

stackoverflow.com

 

[dreid1011]

Thanks dReid, Unfortunately this does not work. As soon as I copy an existing sheet it notes the name if the sheet "as copied" but wont log the name change to the sheet, which i need for a data validation in another tab. Is there another way to achieve this and keep the list updated?

After you copy and rename the sheet, I would assume you are selecting "Sheet Names List" again? Does it not update after you select a new sheet?

 

[dreid1011]

Maybe some more information will help.

Where is ListSheets located and how is it populated?

What is your process from start to finish when you copy/rename a sheet? (IE: Start on Sheet Names List, select sheet to be copied, copy/rename sheet, etc)