BATCH RENAMING OF WORKSHEETS

Our bookkeepers have Excel Workbooks that have Numerous worksheets all labeled as

YYMMDD-JE-PG-1. 2. 3. etc.  (where YYMMDD is the Year, Month, Day i.e. 150331)

There is a SETUP worksheet.

Each month they rename dozens of Tabs

My question, is there a batch method to rename the Tabs or
Can we key the name of the Tabs from a cell on the Setup worksheet
(Using Excel 2003)
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
what do you want to rename to ?
Tj aCommented:
Bill GoldenExecutive Managing MemberAuthor Commented:
Raheman, the tabs are called SETUP, 150331-JE-PG-1, 150331-JE-PG-2, 150331-JE-PG-3, etc.
next month they will be called SETUP, 150430-JE-PG-1, 150430-JE-PG-2, 150430-JE-PG-3, etc.

Takunda, that is a great article but they are selling an excellent tool for Excel 2007, 2010, and 2013.  We are using Excel 2003.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Saurabh Singh TeotiaCommented:
You can use code like this to do what you are looking for..Make the necessary changes in the code where i have left comments basis of your needs...

Also i have assumed your sheet names will be YYMMDD-JE-PG-1

Sub changesheetnames()

    Dim ws As Worksheet
    Dim str As String
    Dim dt As Date

    dt = DateValue("04/30/2015")             '<------You need to enter date here to which you want sheet name to change

    For Each ws In ThisWorkbook.Worksheets

        If InStr(1, ws.Name, "-JE-PG-", vbTextCompare) > 0 Then    '<---This will change sheet names of only those worksheets where it finds '-JE-PG-

            str = Format(dt, "YYMMDD") & Right(ws.Name, Len(ws.Name) - 6)

            ws.Name = str

        End If

    Next ws

End Sub

Open in new window


Saurabh....
Bill GoldenExecutive Managing MemberAuthor Commented:
Saurabh,

I already have the code in PG1, PG2, etc.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    'modify the range A1:A20 to match your input cells
    If Not Intersect(Target, [j8:J46]) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")
        Application.EnableEvents = True
    End If
End Sub

Two questions:

1. How do I add your code and
2. I am not exactly sure what your code does.

I either want to:

A. When I rename the worksheet YYDD-JE-PG-1 to 1503-JE-PG-1,
YYDD-JE-PG-2 is automatically renamed to 1503-JE-PG-2.  Is that what you code does?

B. In the alternative.  My first actual worksheet is a setup sheet called "SETUP."  Can every tab take a four letter code from a cell in the SETUP sheet which renames YYDD-JE-PG-1 to 1503-JE-PG-1 and YYDD-JE-PG-2 to 1503-JE-PG-2 where 1503 is the value of a cell in the SETUP sheet?
Saurabh Singh TeotiaCommented:
BluSkyBill,

Enclosed is the answer...

1. How do I add your code and
The code which you have is currently worksheet change code which means if you change any value in the worksheet, The code gets triggered automatically.. You can't add this code in your current code since you are not changing any value in the worksheet.

2. I am not exactly sure what your code does.
In order to run my code just change couple of variables given in the code and press alt+f8 and run the code of changesheetnames as shown their post changing as it will do what you are looking for...

----------------------------------
A. When I rename the worksheet YYDD-JE-PG-1 to 1503-JE-PG-1,
YYDD-JE-PG-2 is automatically renamed to 1503-JE-PG-2.  Is that what you code does?

You can't do that since you don't have any event which gets triggered when you change sheet names so you need to manually trigger these events by running or calling the code as i explained earlier...

B. In the alternative.  My first actual worksheet is a setup sheet called "SETUP."  Can every tab take a four letter code from a cell in the SETUP sheet which renames YYDD-JE-PG-1 to 1503-JE-PG-1 and YYDD-JE-PG-2 to 1503-JE-PG-2 where 1503 is the value of a cell in the SETUP sheet?

Yes you can link to the sheet of setup..lets say you want to link it to a1 where you enter date in proper date format which is mm/dd/yyyy .. Change this line to this..

 dt =sheets("Setup").range("a1").value

Saurabh...
Bill GoldenExecutive Managing MemberAuthor Commented:
Ok, then how do I add your code to the spreadsheet and keep the other code?
Saurabh Singh TeotiaCommented:
Press alt+f11-->Insert new Module and..copy the code their and paste in the module...

Saurabh...
Bill GoldenExecutive Managing MemberAuthor Commented:
I'm sorry. I cannot get it to change anything regarding the name of the spreadsheet.  
I have attached the entire workbook.
New-Journal-Entry-Master--RBASEGOE10GA--
Saurabh Singh TeotiaCommented:
Their you go your date wasn't in proper format..i made that changes and enclosed is your workbook which does what you are looking for...

Saurabh...
New-Journal-Entry-Master.xls
Bill GoldenExecutive Managing MemberAuthor Commented:
Something is not right here.  I changed the date and nothing happened.  I renamed the tabs back to the YYMMDD- nomenclature (deleted a few of them for brevity) and changed the date and nothing happens.  

Obviously I am just too dumb to understand why this is not working for me!!!

Bill
New-Journal-Entry-Master2.xls
Saurabh Singh TeotiaCommented:
Their you go now i tweaked the macro to run its own as soon you change the dates in H61 earlier you have to run it manually now as soon the dates get changed the macro will change the sheet name...

Saurabh...
New-Journal-Entry-Master2.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
EUREKA! You have done it.  I cannot thank you enough.

Bill
Saurabh Singh TeotiaCommented:
Bill..

Glad to help.. :-)

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.