Announcement

Collapse
No announcement yet.

VBA Macro To Name Sheets Based On Lookup Results

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Macro To Name Sheets Based On Lookup Results

    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.

    Code:
    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

  • #2
    Re: Code To Name Sheets Based On Cell Value From Look Up With Input Box

    Name your Lookup tables are per code;
    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

    Comment


    • #3
      Re: VBA Macro To Name Sheets Based On Lookup Results

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

      Comment


      • #4
        Re: VBA Macro To Name Sheets Based On Lookup Results

        Change;
        Code:
        With WorksheetFunction
        To;
        Code:
        With Application

        Comment


        • #5
          Re: VBA Macro To Name Sheets Based On Lookup Results

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

          Comment


          • #6
            Re: VBA Macro To Name Sheets Based On Lookup Results

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

            Comment

            Working...
            X