Compare worksheet names

**Cadbury3** · 08-02-2020, 05:29 AM

I need to compare the worksheet names in Workbook A with a table in Workbook B. If the worksheet name exists, I need to see if there's an entry in the next column of that table. If nothing, leave as is but if there's something, then I need to replace the worksheet name in Workbook A. Is this possible?

**DocAElstein** · 08-02-2020, 03:06 PM

Hello Cadbury3
Welcome to ExcelFox
Something like that can usually be done in Excel VBA in many ways.
Here is one way.
See if this gets you started
WorkbookA has initially 4 worksheets, named
Sheet1
Sheet2
Sheet3
Sheet4

WorkbookB looks like this

_____ Workbook: WorkbookB.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1	Sheet1
2	Sheet2	x
3	Sheet3
4	MyWorksheet
5

Worksheet: Sheet1

After running the macro, the second worksheet name in WorkbookA is changed to
Sheet2_2

Code:

Option Explicit
Sub CheckRenameWorksheets()
Rem 1 Workbooks data info
Dim WbA As Workbook, WbB As Workbook
 Set WbA = Workbooks("WorkbookA.xls"): Set WbB = Workbooks("WorkbookB.xls")
Dim rngBA As Range: Set rngBA = WbB.Worksheets.Item(1).Range("A1:A4") ' The table range
Dim arrBA() As Variant                                                ' Applying the  .Value  property in the next line will return a field of values. These are held in variant type elements. So the recieving array must be dimensioned appropriately
 Let arrBA() = rngBA.Value                                            ' this is a 2 dimensional , 1 column array  of our table first column
Dim arrBB() As Variant
 Let arrBB() = rngBA.Offset(0, 1).Value                               ' 2 dimensional , 1 column array  of our table second column
Rem loop all worksheets in WorkbookA
Dim Cnt
    For Cnt = 1 To WbA.Worksheets.Count                               ' loop through each tab item
    Dim WsNme As String: Let WsNme = WbA.Worksheets.Item(Cnt).Name    ' the name of the current worksheet under consideration
    Dim MtchedCel As Variant                                          ' Range.Find  will return the found cell within the range, as a range object, or  Nothing  if it does not find the
     Set MtchedCel = rngBA.Find(What:=WsNme, After:=rngBA.Item(1), LookIn:=xlValues, Lookat:=xlWhole, Searchdirection:=xlNext, MatchCase:=True)
        If MtchedCel Is Nothing Then
        ' The worksheet name is not in the table
        Else
            If arrBB(MtchedCel.Row, 1) = "" Then ' There is nothing in the next column
            ' Do nothing
            Else ' There is something
             Let WbA.Worksheets.Item(Cnt).Name = WsNme & "_2"
            End If
        End If
    Next Cnt
End Sub

Alan

**Cadbury3** · 08-02-2020, 04:18 PM

That's almost exactly what I need! Would it be possible instead to change to get the text in B and use that in the rename (so rename to x instead in your example?)