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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,324
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,059
Office Version
  1. 2013
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,177
Members
410,731
Latest member
keobongmacao
Top