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

 

[FormR]

i.e I need all the dates that were a Monday to have the sheet name "Monday" and not "4Jan16".

Hi, you can't have more than one sheet with the same name?

 

[DouglasWicker]

Also tried this but error 13;

If ActiveSheet.Name = Mdate Then
ActiveSheet.Name = "Monday"

End If

 

[DouglasWicker]

Hi, you can't have more than one sheet with the same name?

Sorry yeah, the plan is to add a consecutive number on the end of each sheet name. But I just wanted to make sure it worked with one sheet first.

 

[FormR]

I just wanted to make sure it worked with one sheet first.

You could try something like:

ActiveSheet.Name = Application.Text(ActiveSheet.Name, "dddd")

 

[FormR]

There is probably a more compact way to write it, but here is one option that you can try on a copy of your workbook that does, what I think, you want.

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
For Each ws In ActiveWorkbook.Worksheets
    Select Case Application.Text(ws.Name, "ddd")
        Case "Mon": mo = mo + 1: ws.Name = Application.Text(ws.Name, "dddd") & " " & mo
        Case "Tue": tu = tu + 1: ws.Name = Application.Text(ws.Name, "dddd") & " " & tu
        Case "Wed": we = we + 1: ws.Name = Application.Text(ws.Name, "dddd") & " " & we
        Case "Thu": th = th + 1: ws.Name = Application.Text(ws.Name, "dddd") & " " & th
        Case "Fri": fr = fr + 1: ws.Name = Application.Text(ws.Name, "dddd") & " " & fr
        Case "Sat": sa = sa + 1: ws.Name = Application.Text(ws.Name, "dddd") & " " & sa
        Case "Sun": su = su + 1: ws.Name = Application.Text(ws.Name, "dddd") & " " & su
    End Select
Next ws
End Sub

 

[DouglasWicker]

There is probably a more compact way to write it, but here is one option that you can try on a copy of your workbook that does, what I think, you want.

Thanks, seem to work for most, however some of the tabs that are duplicates are 3Jan17 (2) etc. How can i manipulate this or copy/paste to serve for those tabs?

D

 

[FormR]

Is it a one off exercise? If there's just a few maybe change them manually?

 

[DouglasWicker]

Is it a one off exercise? If there's just a few maybe change them manually?

I wish! unfortunately I actually have another 2200 sheets to sort with about 6 or 700 duplicates :/

I'm wondering if I could run that code you gave me, then run another to remove all the "(2)" from the sheet names and then re-run the code above?

 

[FormR]

I wish! unfortunately I actually have another 2200 sheets to sort

Eeek!

How about this:

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
For Each ws In ActiveWorkbook.Worksheets
    s = Application.Text(Left(ws.Name, 7), "dddd")
    Select Case Left(s, 3)
        Case "Mon": mo = mo + 1: ws.Name = s & " " & mo
        Case "Tue": tu = tu + 1: ws.Name = s & " " & tu
        Case "Wed": we = we + 1: ws.Name = s & " " & we
        Case "Thu": th = th + 1: ws.Name = s & " " & th
        Case "Fri": fr = fr + 1: ws.Name = s & " " & fr
        Case "Sat": sa = sa + 1: ws.Name = s & " " & sa
        Case "Sun": su = su + 1: ws.Name = s & " " & su
    End Select
Next ws
End Sub