Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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.

    VB:
    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,718

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

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

    Re: VBA Macro To Name Sheets Based On Lookup Results

    Change;
    VB:
    With WorksheetFunction 
    
    
    To;
    VB:
    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