changing sheet names in multiple work books

BeccaB131

New Member
Joined
May 31, 2018
Messages
9
Hi Everyone - wondering if someone can help:
Every week i get 20 reports (for vehicle tracking) and the sheet names each week are
Last weeks are titled : Summary, Sat 11 Jul, Sun 12 Jul, Mon 13 Jul, Tue 14 Jul, Wed 15 Jul, Thu 16 Jul, Fri 17 Jul
This weeks: Summary, Sat 18 Jul, Sun 19 Jul, Mon 20 Jul, Tue 21 Jul, Wed 22 Jul, Thu 23 Jul, Fri 24 Jul
Next weeks: Summary, Sat 25 Jul, Sun 26 Jul, Mon 27 Jul, Tue 28 Jul, Wed 29 Jul, Thu 30 Jul, Fri 31 Jul

So i can assess then i drop them into a folder and replace with blank workbook with the same name to allow a 'master spreadsheet' to look up values etc
to do this so that it works i have to open each workbook and manually remove the date and month so it ends up as:
Summary, Sat, Sun, Mon, Tue, Wed, Thur, Fri

Is there a quicker way to rename the sheets each week?

many thanks
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,914
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If each workbook has only those 8 sheets or if there are more sheets none whose name match the pattern in the Like operator of the code below, you could put this code in say your personal workbook and then after you open each workbook, run the code.

Caution: Untested. you may want to test it first on a copy of one workbook.
VBA Code:
Sub ChangeSheetNames()
Dim sht As Worksheet
With ActiveWorkbook
    For Each sht In .Worksheets
        If sht.Name Like "[A-Za-z]" & "[A-Za-z]" & "[A-Za-z]" & " *" Then sht.Name = Left(sht.Name, 3)
    Next sht
End With
End Sub
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@JoeMo @BeccaB131
Joe beat me to it but think it just needs a tweak to have Thur rather than Thu

VBA Code:
Sub ChangeSheetNames()
Dim sht As Worksheet
With ActiveWorkbook
    For Each sht In .Worksheets
        If sht.Name Like "[A-Za-z]" & "[A-Za-z]" & "[A-Za-z]" & " *" Then sht.Name = Left(sht.Name, 3)
        If sht.Name = "Thu" Then sht.Name = sht.Name & "r"
       
    Next sht
End With
End Sub
Hope that helps.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,604
Messages
5,838,332
Members
430,538
Latest member
PedroOliveira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top