Testing 600+ sheetnames

[DouglasWicker]

Morning all

I have a workbook of 600+ sheets all named by the date the sheet refers to (in the form 1Jan17).

I am trying to use vba to change the name of the sheets to the dates respective weekday. i.e I need all the dates that were a Monday to have the sheet name "Monday" and not "4Jan16".

I have a list of dates that were mondays, tuesdays etc.

Trying to assign the all the dates in string format to a variable, and then run code to check whether the sheet is called this, if so, change sheetname, if not, test next sheet.

I thought of this code as a guess but not working (this code is just for mondays).


Sub convertabstodates()


Dim Mdate As String
Dim rev As String


Mdate = "4Jan16" & "," & "11Jan16" & "," & "18Jan16" & "," & "25Jan16" & "," & "1Feb16" & "," & "8Feb16" & "," & "15Feb16" & "," & "22Feb16" & "," & "29Feb16" & "," & "7Mar16" & "," & "14Mar16" & "," & "21Mar16" & "," & "28Mar16" & "," & "4Apr16" & "," & "11Apr16" & "," & "18Apr16" & "," & "25Apr16" & "," & "2May16" & "," & "9May16" & "," & "16May16" & "," & "23May16" & "," & "30May16" & "," & "6Jun16" & "," & "13Jun16" & "," & "20Jun16" & "," & "27Jun16" & "," & "4Jul16" & "," & "11Jul16" & "," & "18Jul16" & "," & "25Jul16" & "," & "1Aug16" & "," & "8Aug16" & "," & "15Aug16" & "," & "22Aug16" & "," & "29Aug16" & "," & "5Sep16" & "," & "12Sep16" & "," & "19Sep16" & "," & "26Sep16" & "," & "3Oct16" & "," & "10Oct16" & "," & "17Oct16" & "," & "24Oct16" & "," & "31Oct16" & "," & "7Nov16" & "," & "14Nov16" & "," & "21Nov16" & "," & "28Nov16" & "," & "5Dec16" & "," & "12Dec16" & "," & "19Dec16" & "," & "26Dec16"


If InStr(Mdate, ActiveSheet.Mame) <> 0 Then
ActiveSheet.Name = "Monday"


End If


End Sub



All help appreciated

 

[DouglasWicker]

Eeek!

How about this:

No luck with that one :/ just getting an error.

I have used this code (below) before to get rid of prefix's but cant get it to work with a suffix;

Sub removeduplicates()


Dim ws As Worksheet
Const Suffix As String = " (2)*"
For Each ws In Worksheets
If ws.Name Like Suffix & "*" Then ws.Name = Mid(ws.Name, Len(Suffix) + 1)
Next


End Sub

 

[FormR]

No luck with that one :/ just getting an error.

Hi, that's not enough information to be able to help find the cause of the error I'm afraid.

 

[Fluff]

How about this modified version of FormR's code

Sub convertabstodates()
Dim ws As Worksheet, mo As Long, tu As Long, we As Long, th As Long, fr As Long, sa As Long, su As Long, s As String, r As String
For Each ws In ActiveWorkbook.Worksheets
    s = Application.Text(Left(ws.Name, 7), "dddd")
    If Right(ws.Name, 1) = ")" Then r = Right(ws.Name, 4) Else r = ""
    Select Case Left(s, 3)
        Case "Mon": mo = mo + 1: ws.Name = s & " " & mo & r
        Case "Tue": tu = tu + 1: ws.Name = s & " " & tu & r
        Case "Wed": we = we + 1: ws.Name = s & " " & we & r
        Case "Thu": th = th + 1: ws.Name = s & " " & th & r
        Case "Fri": fr = fr + 1: ws.Name = s & " " & fr & r
        Case "Sat": sa = sa + 1: ws.Name = s & " " & sa & r
        Case "Sun": su = su + 1: ws.Name = s & " " & su & r
    End Select
Next ws
End Sub

 

[DouglasWicker]

Hi, that's not enough information to be able to help find the cause of the error I'm afraid.

How about this modified version of FormR's code

That last one worked! thanks guys both really helpful. Lots of duplicates after the new tab names but I can filter them with kutools now so thats great thank you!