changing sheet names in multiple work books

[BeccaB131]

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

 

[JoeMo]

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.

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]

@JoeMo @BeccaB131
Joe beat me to it but think it just needs a tweak to have Thur rather than Thu

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.

 

[BeccaB131]

Works great thanks for your help -