Check if worksheet tab name is already in use in another workbook

[Martin_H]

Hello folks,

I have been using this pair of code to check, if worksheet tab name exists.

It is based on the G2 value inside the Worksheet_One. (Worksheet_One is a part of workbook called Workbook_One).

Function WorksheetExists2(WorksheetName As String, Optional wb As Workbook) As Boolean
    If wb Is Nothing Then Set wb = ThisWorkbook
    With wb
        On Error Resume Next
        WorksheetExists2 = (.Sheets(WorksheetName).Name = WorksheetName)
        On Error GoTo 0
    End With
End Function

    If WorksheetExists2(Worksheets("Worksheet_One").Range("G2").Value) Then
    MsgBox "Tab name already exists.", vbCritical
    Exit Sub
    Else
    End If

Now I am trying to figure out a code, that will check existence of worksheet tab name in another Workbook based on cell value in different Workbook. Let me explain.

I have a workbook called Workbook_One with worksheet called Worksheet_One. Inside the Worksheet_One there is the cell G2 which is occupied with today's date.
There is also a workbook called Workbook_Two with multiple worksheets. Each worksheet tab is named with a specific date.

By runing this not-yet-existing-macro I want to check if worksheet tab name has been already used in Workbook_Two by the cell G2 value inside Worksheet_One, which is inside Workbook_One.

I hope I explained it well.

Help is much appreciated.


Thank you!

 

[Fluff]

How about

If WorksheetExists2(Worksheets("Worksheet_One").Range("G2").Value, Workbooks("Workbook_Two.xlsm")) Then

Make sure the name of the other workbook is correct(including extension), also it needs to be open.

 

[Martin_H]

Hi Fluff, thank you for your quick response.

Would it be possible to refer to Workbook_Two.xlsm not by its full name but by cell (A5) value?

 

[Fluff]

As long as A5 has the full name, yes.

 

[Martin_H]

   If WorksheetExists2(Worksheets("Worksheet_One").Range("G2").Value, Workbooks(Worksheets("Worksheet_One").Range("A5").Value)) Then

Should do the trick.

 

[Fluff]

Yup that should work.