Rename Tab Based on Cell Format

[Chad Bateman]

Hello,

I'm trying to find some code that can rename a tab based on a certain cell's contents.

I have a random cell (the row is completely random, the column will always be Column A).

The cell is always an account number- like this: "Account Number 1234567"- the first 16 characters will always be the same, so I'm thinking looking for some VBA code that can recognize the first 16 characters are "Account Number ", and name the sheet the account number "1234567" in this example.

The account number may vary in length, so the tab should be named whatever characters 17-End are (so I guess it would just remove the first 16 characters?).

I don't expect anyone to build this code from scratch for me (you know...unless you want to - I'm thinking you guys can post whatever similar code you have and I can modify it to meet this purpose?

Thanks in advance,

Chad

[fredlo2010]

Hi hope this helps

Option Explicit

Sub RenameSheet()

    Const strKEY_WORD As String = "Account Number"

    Dim sh As Worksheet
    Dim rToSearch As Range
    Dim rFound As Range

    Set sh = ActiveSheet
    Set rToSearch = sh.Columns("A").Resize(sh.UsedRange.Rows.Count)


    ' Find the row with the key word.
    Set rFound = rToSearch.Find(What:=strKEY_WORD, LookIn:=xlValues, LookAt:=xlPart, _
                                SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=True, MatchByte:=False, SearchFormat:=False)

    ' Check if we found it
    If Not rFound Is Nothing Then
        sh.Name = Replace$(rFound.Value, strKEY_WORD, vbNullString)
    End If

End Sub

[Chad Bateman]

This worked perfectly, and you're my VBA HERO!

Thanks so much!

[fredlo2010]

I am glad I was able to help.