Renaming Multiple Worksheets within Workbook

[crisbyy]

I have just taken over a new job at the hospital system I work for to annually inspect the Medical Gas equipment in each patient room of our many hospitals. In recent years different people have been doing them each year, who really didn't care, and the patient rooms and different floors are all out of order on the workbooks, yet the worksheet tab names are counted Patient Outlet Page (1), Patient Outlet Page (2), Patient Outlet Page (3), etc..I had to move the sheets around to put them in their actual order based on floor and patient room number, but now the worksheet names are out of order. Is there a way to rename multiple worksheets(all 87 of them) within an entire workbook?

[Domski]

Sure, what do you want to call them now?

Dom

[crisbyy]

I would like them to be:

Patient Outlet 1, Patient Outlet 2, Patient Outlet 3, etc..all the way to Patient Outlet 87


there are worksheets before "Patient Outlet 1" that need to stay the same, and there are worksheets after "Patient Outlet 87"


If it helps, the sheet after "Patient Outlet 87" is called "Patient Outlet Totals" and it lists how many outlets are on each worksheet.

The names that are in cells A5-A91 are "Patient Outlet 1" through "Patient Outlet 87"

[Domski]

This will rename any sheet that's name use to start with Patient Outlet Page to Patient Outlet 1, Patient Outlet 2 etc

Please Login or Register  to view this content.

Dom

[crisbyy]

Thanks! I'm sure that works correctly but when I entered it into vba and clicked run, nothing happened.

[Domski]

See the attached example.

Dom

[crisbyy]

Sweet! It worked! I didn't have the Option Explicit code. Anyways, I really appreciate the help and it will save a lot of time in the future!

[Domski]

No worries. Option Explicit isn't actually part of the code but a setting in VBA that forces you to declare variables within your code so shouldn't have made any difference. Not sure why it wouldn't have worked before.

Dom