VBA Macro Problem

[Andy0311]

Hello,

I work for an online school and supervise 31 teachers. Twice a month, I run a report which gives me the number of hours each teacher has spent online. I output the report in an excel workbook. The output has 31 tabs for each teacher's information. The tabs are Sheet 1, Sheet 2, etc. I recorded a macro renaming each tab with the name of the teacher, so I wouldn't have to type each teacher's name every time I ran the report. Whe I tried to run the macro I got this error message:

Run-time error '9':

Subscript out of range

When I clicked on debug (I cannot debug VBA), I got this:

Sub SeatTabs()
'
' SeatTabs Macro
'

'
Sheets("1").Select
Sheets("1").Name = "Allen-Harmon"
Sheets("2").Select
Sheets("2").Name = "Allison"
Sheets("3").Select
Sheets("3").Name = "Baratko"
Sheets("4").Select
Sheets("4").Name = "Barot"
Sheets("5").Select
Sheets("5").Name = "BeisnerA"
Sheets("6").Select
Sheets("6").Name = "BeisnerD"
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "BurchfieldG"
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "BurchfieldT"
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "Elliott"
Sheets("Sheet10").Select
Sheets("Sheet10").Name = "Fogle"
Sheets("Sheet11").Select
Sheets("Sheet11").Name = "Goubeaux"
Sheets("Sheet12").Select
Sheets("Sheet12").Name = "GriffithJ"
Sheets("Sheet13").Select
Sheets("Sheet13").Name = "GriffithM"
Sheets("Sheet14").Select
Sheets("Sheet14").Name = "Harris"
Sheets("Sheet15").Select
Sheets("Sheet15").Name = "Hellemann"
Sheets("Sheet16").Select
Sheets("Sheet16").Name = "HelsingerJ"
Sheets("Sheet17").Select
Sheets("Sheet17").Name = "HelsingerP"
Sheets("Sheet18").Select
Sheets("Sheet18").Name = "Jones"
Sheets("Sheet19").Select
Sheets("Sheet19").Name = "Koch"
Sheets("Sheet20").Select
Sheets("Sheet20").Name = "Leibold"
Sheets("Sheet21").Select
Sheets("Sheet21").Name = "Little"
Sheets("Sheet22").Select
Sheets("Sheet22").Name = "Lorz"
Sheets("Sheet23").Select
Sheets("Sheet23").Name = "MerkC"
Sheets("Sheet24").Select
Sheets("Sheet24").Name = "MerkJ"
Sheets("Sheet25").Select
Sheets("Sheet25").Name = "Miller"
Sheets("Sheet26").Select
Sheets("Sheet26").Name = "Mitter"
Sheets("Sheet27").Select
Sheets("Sheet27").Name = "Saylor"
Sheets("Sheet28").Select
Sheets("Sheet28").Name = "Seitz"
Sheets("Sheet29").Select
Sheets("Sheet29").Name = "Showalter"
Sheets("Sheet30").Select
Sheets("Sheet30").Name = "Statt"
Sheets("Sheet31").Select
Sheets("Sheet31").Name = "Swiger"
Range("M31").Select
End Sub


I don't know how to proceed at this point, or if the macro I need is possible. Any help would be appreciated. Thanks, again.

Andy

 

[Fluff]

When you clicked Debug, which line of code was highlighted in Yellow?

 

[jamescooper]

It is because in the first 10 sheets you do not have the sheet name, i.e.

Sub Rename

[COLOR=#000000][FONT=Arial]Sheets("[/FONT][/COLOR][COLOR=#ff0000][FONT=Arial]Sheet1[/FONT][/COLOR][COLOR=#000000][FONT=Arial]").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("[/FONT][/COLOR][COLOR=#ff0000][FONT=Arial]Sheet1[/FONT][/COLOR][COLOR=#000000][FONT=Arial]").Name = "Allen-Harmon"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("[/FONT][/COLOR][COLOR=#ff0000][FONT=Arial]Sheet2[/FONT][/COLOR][COLOR=#000000][FONT=Arial]").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("[/FONT][/COLOR][COLOR=#ff0000][FONT=Arial]Sheet2[/FONT][/COLOR][COLOR=#000000][FONT=Arial]").Name = "Allison"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("3").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("3").Name = "Baratko"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("4").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("4").Name = "Barot"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("5").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("5").Name = "BeisnerA"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("6").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("6").Name = "BeisnerD"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet7").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet7").Name = "BurchfieldG"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet8").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet8").Name = "BurchfieldT"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet9").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet9").Name = "Elliott"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet10").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet10").Name = "Fogle"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet11").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet11").Name = "Goubeaux"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet12").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet12").Name = "GriffithJ"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet13").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet13").Name = "GriffithM"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet14").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet14").Name = "Harris"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet15").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet15").Name = "Hellemann"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet16").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet16").Name = "HelsingerJ"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet17").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet17").Name = "HelsingerP"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet18").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet18").Name = "Jones"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet19").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet19").Name = "Koch"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet20").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet20").Name = "Leibold"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet21").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet21").Name = "Little"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet22").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet22").Name = "Lorz"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet23").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet23").Name = "MerkC"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet24").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet24").Name = "MerkJ"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet25").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet25").Name = "Miller"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet26").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet26").Name = "Mitter"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet27").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet27").Name = "Saylor"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet28").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet28").Name = "Seitz"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet29").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet29").Name = "Showalter"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet30").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet30").Name = "Statt"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet31").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheets("Sheet31").Name = "Swiger"[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Range("M31").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]
End Sub

You could just list all the names in a separate master sheet if you want to edit it in future?[/FONT][/COLOR]

 

[Fluff]

Is there anything in the sheets that shows whose sheet it is?

 

[Andy0311]

The first line, Sheets("1").Select.

 

[Andy0311]

Yes, a report with the teacher's name.

 

[Fluff]

Which cell has the name & do you want this to work on every sheet in the workbook?

 

[Andy0311]

B5, and yes.

 

[Fluff]

In that case try

Sub Andy0311()
    Dim Ws As Worksheet
    
    For Each Ws In Worksheets
        Ws.Name = Ws.Range("B5").Value
    Next Ws
End Sub

This assumes that the names are all shorter than 31 characters & contain no illegal characters

 

[Andy0311]

I renamed the sheets properly and it ran perfectly. Thanks for your help. This will save me so much times.

Andy