Rename a series of open workbooks as Project1, Project 2 and so on based on a criteria.

I want to rename a series of "Open workbooks" based on a criteria. That criteria would be if a cell in the workbook contains certain word. In this attached example it would be : A1 = "Personnel Record Data". If A1 = Personnel Record Data in any of the open workbook it should rename and save it as Project1( It should close the file though, not sure if it will throw a debug if it runs again on the workbook that is already renamed). Now the first file is Project1, the next file it finds that meets the criteria it should rename it as Project2 and go on with the count. Any file it doesn't meet the criteria, it should skip and move ahead. Is that possible? I'm attaching sample workbooks. One which meets the criteria and one which doesn't.

This should work on all the open workbooks.

Personal Record Data.xlsPersonal Data.xls

Maybe:

Please Login or Register  to view this content.

Its working fine, but at the end of renaming all the files, it gives a debug as I dont want to Close the workbooks. I want them to stay open. Anything I need to add for a fix?

Take out the line:

Please Login or Register  to view this content.

I did take out that line. Its after I take out this line it give me a debug. It says that the subscript is out of range. Maybe its not exiting the loop?

Do all the open workbooks have a "Sheet1"?

Yes, they do. Infact , that was a dummy example I gave in. They have a specific sheet name. They are named Work in Process. And I have replaced Sheet1 with Work in Process. But, while running the macro, there will be other excel sheets open which do not match this criteria. That is, the sheet name is not Work in Process. I do not want any action on them. I want VBA to skip those files. Any work around?

Before the Loop add:

On Error Resume Next

After the Loop add:

On Error GoTo 0

John,

I really appreciate your time and effort. Now I have one more problem. Its renaming all the open workbooks. Can we add something into the loop which would .. say if the sheet name = Work in process and A17 is equal to Work in Process then rename?? Or is it that I'm adding the On Error function in a wrong place?

Maybe:

Please Login or Register  to view this content.

Sorry, but its not accepting the "Range.Value =", Its ignoring and renaming all the file.

It worked when I tested it can you post your code?