Consulting

Results 1 to 4 of 4

Thread: Renaming Worksheets Using VBA

  1. #1

    Renaming Worksheets Using VBA

    Hi VBA Experts,

    I have a simple VBA code to rename worksheets. I listed it below. I created a list in the Range("C8:C272"). I do not have any duplicate names in the list. However, I am getting this message.

    "Run-time error '1004':

    That name already taken. Try a different one."


    I used conditional formatting to check for duplicates and it doesn't find any. Any idea what could be going wrong?

    VBA CODE:

    Sub RenameSheets()


    Dim c As Range
    Dim J As Integer


    J = 9
    For Each c In Range("C8:C272")


    J = J + 1

    If Sheets(J).Name = "List" Then J = J + 1
    Sheets(J).Name = c.Text

    Next c


    End Sub

  2. #2
    It looks like you want to change all the sheets except a sheet names "List" Is that right.
    It does not matter which sheet is renamed 1st, 2nd, 3rd etc etc?
    How many sheets in the workbook? 266?

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Option Explicit
    
    Sub RenameSheets()
    Dim c As Range
    Dim J As Long
    
    J = 9
    
    For Each c In Range("C8:C272")
       If Not Sheets(J).Name = "List" Then 'If the name is "List" then do nothing
           J = J + 1
            Sheets(J).Name = c.Text
       End If
    Next c
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    It is always nice to hear if a suggestion works but in this case no such luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •