0 votes
in VBA by Beginner (15 points)
edited by

Hi guys

How would I get the following to work? I have a list and was hoping to check if a worksheet name has a match in Column A of the list and get the corresponding in B.

At the moment, all I can think of is doing it name by name.

Sheets("One").Name = "OneNewOne"

Only issue is it might get rather long and I might miss something. Can someone please help?

Much appreciated!

by Expert (911 points)
When you post a question, please oblige us by responding to comments and answers.

1 Answer

0 votes
by Expert (911 points)
edited by

If I understand, the active sheet has existing sheet names in column A and new sheet names in column B. You want a macro that will rename each sheet listed in column A using the new name listed in column B. If the sheet in column A does not exist in the active workbook or the new name in column B is blank, it will be skipped.

Sub RenameSheets()
' assume ActiveSheet has sheet names and renames
' assume sheet names in column A (1) and renames in column B (2)
' assume row 1 is a header and names begin in row 2
    Dim sName As String, sRename As String, msg As String
    Dim lastRow As Long, n As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    msg = vbNullString
    On Error Resume Next
    For n = 2 To lastRow
        sName = Cells(n, 1)
        sRename = Trim(Cells(n, 2))
        If sRename <> vbNullString Then Sheets(sName).Name = sRename
        If Err Then msg = msg & vbNewLine & sName
    Next n
    If msg <> vbNullString Then MsgBox "Could not rename:" & msg
End Sub

Does this answer your question?

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.