Results 1 to 5 of 5

VBA Change sheet name

This is a discussion on VBA Change sheet name within the Excel Questions forums, part of the Question Forums category; I have looked high and low in Searches but can't find a solution. I have a workbook with close to ...

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,037

    Default VBA Change sheet name

    I have looked high and low in Searches but can't find a solution.

    I have a workbook with close to 100 sheets all named a certain way. Management has decided to change part of the name that was used.

    Currently my sheets are named as such, using the letter x to denote letters:
    xxxx 4401 x
    xxxx 4401 xxx
    4401 xxx
    4401xxxx
    OR
    xxxx 5503 x
    xxxx 5503 xxx
    5503 xxx xx
    5503xxxx


    What they want now is to change the number designator to a different one. Like so:

    Sheet nameChange to
    xxxx 4401 xxxxx 1532 x
    xxxx 4401 xxxxxxx 1532 xxx
    4401 xxx1532 xxx
    4401xxxx1532xxxx
    OR
    xxxx 5503 xxxxx 1701 x
    xxxx 5503 xxxxxxx 1701 xxx
    5503 xxx xx1701 xxx xx
    5503xxxx1701xxxx


    This is only an example of only two changes that need to be made but there are at least 10 to do. And one number may be found in anywhere from 6-8 sheets.

    I was thinking of an If/Else type of procedure, but am not quite sure on how to do it because I think a wildcard would have to come into play to account for the letters.

    One thing that might help is, the numbers will only be at the start of the sheet name or 5 spaces to the right, and will only be 4 digits long.

    Any guidance would be appeciated.
    Show your sheet on the board, get MrExcel HTML Maker or Excel Jeanie
    Search MrExcel through Google Here

    Using Excel 2007 on Windows 7

  2. #2
    Board Regular nightcrawler23's Avatar
    Join Date
    Sep 2009
    Location
    singapore
    Posts
    720

    Thumbs up Re: VBA Change sheet name

    Code:
    Sub SheetNames()
    Dim currNum, newNum As Integer
    Dim myName As String
        currNum = InputBox("Number to be Replace")
        newNum = InputBox("New Number")
        For Each sh In ActiveWorkbook.Sheets
            myName = sh.Name
            myName = WorksheetFunction.Substitute(myName, currNum, newNum)
            sh.Name = myName
        Next sh
    End Sub
    Try this

  3. #3
    Board Regular nightcrawler23's Avatar
    Join Date
    Sep 2009
    Location
    singapore
    Posts
    720

    Default Re: VBA Change sheet name

    Plz make a back up before running any code.

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,037

    Default Re: VBA Change sheet name

    Thank you nc23

    I did make a copy before trying. Works great. Yes I have to run it for all 10 + changes, but that's okay. One time shot I hope.

    Just one thing I don't understand with the code. How did it determine where the numbers were.

    Is it the declaration of:
    Dim currNum, newNum As Integer
    Show your sheet on the board, get MrExcel HTML Maker or Excel Jeanie
    Search MrExcel through Google Here

    Using Excel 2007 on Windows 7

  5. #5
    Board Regular nightcrawler23's Avatar
    Join Date
    Sep 2009
    Location
    singapore
    Posts
    720

    Default Re: VBA Change sheet name

    NO.Those statements are to define variables.
    I stored the sheetname in a variable by the code
    myName = sh.Name
    and used the Excel Substitute function to replace ur old number to new number.It itself looks for the paatern provided by u in currNum, and replace it with newNum.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com