Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: VBA Macro To Name Sheets Based On Lookup Results

  1. #1
    Join Date
    11th February 2009
    Location
    USA (Texas)
    Posts
    44

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,786

    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

  3. #3
    Join Date
    11th February 2009
    Location
    USA (Texas)
    Posts
    44

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,786

    Re: VBA Macro To Name Sheets Based On Lookup Results

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

  5. #5
    Join Date
    11th February 2009
    Location
    USA (Texas)
    Posts
    44

    Re: VBA Macro To Name Sheets Based On Lookup Results

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    11th February 2009
    Location
    USA (Texas)
    Posts
    44

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: November 19th, 2009, 00:00
  2. Lookup Number Results in #N/A
    By AnnaVilj in forum EXCEL HELP
    Replies: 4
    Last Post: September 8th, 2007, 14:29
  3. Concatenated All Results Of Lookup
    By tabanowa in forum EXCEL HELP
    Replies: 12
    Last Post: August 31st, 2007, 04:55
  4. Sum All Results Of Lookup
    By tabanowa in forum EXCEL HELP
    Replies: 4
    Last Post: August 30th, 2007, 06:31
  5. Replies: 14
    Last Post: March 18th, 2006, 05:59

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno