VBA Macro To Name Sheets Based On Lookup Results

[RG26]

I want to name three sheets (sheets will have a different name every month so I refer to them below as Sheet(1), Sheet(2), and Sheet(3) (Their sheet index)) using a vlookup in VBA. I want the code to promt the user for input and based on the entry, I want to use a vlookup to name the sheets. There are three "lookup tables" on a sheet named "Ref" that I have defined as Table1 (used for naming Sheet(1)), Table2 (used for naming Sheet(2)), and Table3 (used for naming Sheet(3)) that I want to reference in the code.
I also want a code to check the first MyLookUp1 which I have attemped below, but I do not know if I am doing this correctly.

Below is my bad VBA attempt so you get an idea of what I am trying to do. Also, attached is an exaple file.

Sub namesheets() 
    Dim MyEntry As Variant 
    Dim MyLookUp1 As Variant 
    Dim MyLookup2 As Variant 
    Dim MyLookup3 As Variant 
    Dim MySTRING As String 
    MyEntry = Application.InputBox( _ 
    Prompt:="Please enter an Item:", _ 
    Title:="Lookup sheet name", _ 
    Type:=2) 
    MySTRING = MyEntry 
     
    MyLookUp1 = Application.VLookup(MySTRING, Table1, 2, False) 
    If IsError(MyLookUp1) Then Exit Sub 
     
    Sheets(1).Select 
    ActiveSheet.Name = MyLookUp1 
     
    MyLookup2 = Application.VLookup(MySTRING, Table2, 2, False) 
    Sheets(2).Select 
    ActiveSheet.Name = MyLookup2 
     
    MyLookup3 = Application.VLookup(MySTRING, Table3, 2, False) 
    Sheets(3).Select 
    ActiveSheet.Name = MyLookup3 
     
End Sub 

[Dave Hawley]

Name your Lookup tables are per code;

Sub namesheets() 
    Dim MyEntry As String 
    Dim MyLookUp1 As String 
    Dim MyLookup2 As String 
    Dim MyLookup3 As String 
     
     
    MyEntry = InputBox( _ 
    Prompt:="Please enter an Item:", _ 
    Title:="Lookup sheet name") 
     
    If MyEntry = vbNullString Then Exit Sub 
     
     
     
    With WorksheetFunction 
        If .CountIf(Range("Table1").Columns(1), MyEntry) > 0 Then 
            MyLookUp1 = .VLookup(MyEntry, Range("Table1"), 2, False) 
            Sheets(1).Name = MyLookUp1 
        End If 
         
        If .CountIf(Range("Table2").Columns(1), MyEntry) > 0 Then 
            MyLookup2 = .VLookup(MyEntry, Range("Table2"), 2, False) 
            Sheets(2).Name = MyLookup2 
        End If 
         
        If .CountIf(Range("Table3").Columns(1), MyEntry) > 0 Then 
            MyLookup3 = .VLookup(MyEntry, Range("Table3"), 2, False) 
            Sheets(3).Name = MyLookup3 
        End If 
    End With 
     
     
End Sub 

[RG26]

Thanks. I ran the code and I am getting an error message stating: "Unable to get the Vlookup property of the WorksheetFunction class.

[Dave Hawley]

Change;

With WorksheetFunction 

To;

With Application 

[RG26]

Thanks. I ran the code and now the error message states: "type mismatch".

[RG26]

I changed the values of the left most column of the three "look up tables" to text and the macro ran successfully! Thanks