Renaming Worksheet in VBA

1
votes

I'm having trouble with renaming a worksheet in VBA.

I'm writing a Sub that needs to check if there is a worksheet in the workbook that has a specific name, and if there isn't then make a new sheet with this name. I get the runtime error 1004 application defined or object defined error. I'm hoping anyone can help me. Here is my code:

Dim ws As Worksheet 
Dim ArrayElement As Variant 
Dim Current_Tabs(1 To 10) As String 

NumberNewTab = 1 
Tab_Name_Current_Game = Echt_team1 + "vs. " + Echt_team2

For Each ws In Worksheets 'For every worksheet in this workbook
  Found = False 
  For Each ArrayElement In Current_Tabs array
      If Tab_Name_Current_Game = ArrayElement Then 
      Worksheets(ws).Activate
      Worksheets.Select
      Found = True
      End If
      Next ArrayElement

      If Found = False Then 
         Worksheets.Add
         Sheets(1).Name = Tab_Name_Current_Game **'Here I get the error**
         Current_Tabs(NumberNewTab) = Tab_Name_Current_Game
         NumberNewTab = NumberNewTab + 1
      End If Next ws

If I use Sheets(1).Name = "Test" then I don't run into errors. I don't get why this would be a problem for VBA. Hoping someone can help me. Thank you!

2
excelvba
What does this Echt_team1 + "vs. " + Echt_team2 equate to on the first run ? – 99moorem
You should concatenate strings by using ampersands. Tab_Name_Current_Game = Echt_team1 & "vs. " & Echt_team2 – luke_t
What is the purpose of the word array in this line: For Each ArrayElement In Current_Tabs array? It should not compile. – mielk
@99moorem : these are teamnames retrieved from a website. – Matsertt
@Iturner I het the same problem when I do this – Matsertt

2 Answers

0
votes

You never defined Tab_Name_Current_Game. Add Dim Tab_Name_Current_Game as String to your declarations at the top. Then see what happens.

0
votes

You can make it much easier:

Public Sub renameSheet()
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim Tab_Name_Current_Game As String


    Set wb = Excel.ActiveWorkbook

    'The line below is copied from your code, but I don't like it. 
    'You should pass Echt_team1 and Echt_team2 as input parameters to this Sub.
    Tab_Name_Current_Game = Echt_team1 + "vs. " + Echt_team2 


    On Error Resume Next
    Set ws = wb.Worksheets(Tab_Name_Current_Game)
    On Error GoTo 0


    If ws Is Nothing Then
        Set ws = wb.Worksheets.Add
        ws.Name = Tab_Name_Current_Game
    End If


End Sub

To make the code more reliable, you can also add this function to your code: Function to check if sheet name is valid, and change this line:

        ws.Name = Tab_Name_Current_Game

to

        ws.Name = legalSheetName(Tab_Name_Current_Game)

It will ensure that the name you are trying to assign to a worksheet is not too long and has no illegal characters.