Dynamically change multiple sheet names based upon a cell's value

Hi,

I'm trying to write a simple macro that will enable the 13 sheet names in a workbook to change based on what the user enters in a cell for what the fiscal year is (our fiscal year is April to March). Currently the 13 sheets are called "April 2017-18, May 2017-18, June 2017-18...March 2018-19", and "Year to Date 2017-18". There is a 14th sheet called "setup" where the user enters data used in some or all of the monthly sheets.

What I want the user to do is enter the fiscal year in cell A1 of the "setup" sheet (e.g.: user enters "2018-19") as this gets used in the title of all the sheets, but I also want this to trigger name changes to the sheets.

This will save me from having to update the template's sheet names each year by allowing the users to do it themselves.

I thank you in advance for the wonderful advice!

Hi Alan.
Sorry about that.
Thanks.

Thank you both very much for the quick responses!

Both pieces of code work as is. I figured it would be useful to also have a sheet with instructions; hence, I need to also exclude it from being updated with the fiscal year.


On Alan's code I changed it to

Please Login or Register  to view this content.

and it works.


On jolivane's code I changed it to

Please Login or Register  to view this content.

and receive an error message "Invalid procedure call or argument".

I don't understand why an error on one, but not the other. Could one of you enlighten me? Thanks.

I also think it needs to be "And" instead of "Or"

I copied/pasted the sheet name into the VBA code, so I know they are exactly the same, but that didn't resolve the error. HOWEVER, when I changed the "Or" to "And", it works like a charm! THANKS!!