Below are the steps I use to change the contents of a cell in one worksheet.
I open my first excel worksheet in my folder. The path is C:\Users\Entities
I go to Review tab and select Protect Workbook
I am prompted to enter Password
I enter password “15bb”
I right click on the tab named Instructions and unhide the sheet named “Constants”
On the Constants sheet, I change the name on cell B1 to the new name on my list
(note cell B1 has a range name equal to “EntityNumber”)
I right click on the tab named Constants and I hide the tab.
I then click on Protect Workbook
I enter the Password “15bb” and click on OK
I reenter the password “15bb”
I save the worksheet and close it
I then do the above steps for each of the remaining 349 plus worksheets.
Attached is a worksheet named EntityLookup.xlsx that list the original name on each of my worksheets and the new name that I want on Constants sheet cell B1. I am also including three worksheet samples for which I want to change the contents of cell B1 on my Constants sheet. Note, the name on one of my samples does not exists in EntityLookup.xlsx. For entities that are not on my Entity Lookup list, I would want the code to close the worksheet without saving the worksheet and go to the next worksheet.
Q1 is there a Macro or Code that I can use to perform the above steps?
Q2 Can the macro/code due this by referring to the range named “EntityNumber” and reduce some of the above steps?