Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA code to get worksheet name

  1. #1
    Join Date
    8th July 2006
    Posts
    51

    VBA code to get worksheet name

    Hello Everyone,

    I now have the vba code formula thanks to Domenic.
    VB:
    Range("B10").Select 
     
    ActiveCell.FormulaR1C1 = _ 
     
    "=LOOKUP(2,1/(('Worksheets(2)'!R[1]C[1]:R[42]C[1]<>""Total"")*('Worksheets(2)'!R[1]C[39]:R[42]C[39]>=1%)),'Planner date 6-9-2006'!R[1]C[1]:R[42]C[1])" 
    
    


    Now do I dynamically get a different worksheet name?

    I have to have this since it is imported to this workbook.

    Big thanks again to Domenic and Tom.

    Thanks in advance for any help on this! : D

    Kurt
    Last edited by royUK; July 8th, 2006 at 15:08.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th July 2006
    Posts
    51

    Re: VBA code to get worksheet name

    Hello,

    I found some code here from ozgrid.com

    Can someone help me put these together?
    VB:
    Function SheetName(rCell As Range, Optional UseAsRef As Boolean) As String 
        Application.Volatile 
        If UseAsRef = True Then 
            SheetName = "'" & rCell.Parent.name & "'!" 
        Else 
            SheetName = rCell.Parent.name 
        End If 
    End Function 
    
    

    Thanks,

    Kurt
    Last edited by royUK; July 8th, 2006 at 15:08.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th July 2005
    Posts
    214

    Re: VBA code to get worksheet name

    Hi,

    I'm not sure what factor determines what sheet name to use unless it is always the "active worksheet" name, regardless of the workbook. And, where should the active sheets name go in your formula?

    I suppose you can use
    ActiveSheet.Name
    to get the sheet name. I would think that you should be able to built that into your formula. As said, I'm not sure where though.

    "=LOOKUP(2,1/((" & ActiveSheet.Name & "...

    Stefan

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th March 2005
    Posts
    507

    Re: VBA code to get worksheet name

    Input your worksheet name in cell A1 then run this code below.


    VB:
    Sub LOOKUP() 
         
        Range("A9").Formula = "=LOOKUP(2,1/((' " & Range("A1") & "'!C11:C52<>""Total"")*(' " & Range("A1") & "'!AO11:AO52>=1%)),' " & Range("A1") & "'!C11:C52)" 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th March 2005
    Posts
    507

    Re: VBA code to get worksheet name

    Here are 2 other options you might want to try.

    VB:
    Sub LOOKUP_2() 
        Dim Sheet_Name As String 
        Sheet_Name = ActiveSheet.Name 
         
        Range("A9").Formula = "=LOOKUP(2,1/(('" & Sheet_Name & "'!C11:C52<>""Total"")*('" & Sheet_Name & "'!AO11:AO52>=1%)),'" & Sheet_Name & "'!C11:C52)" 
         
    End Sub 
    
    

    OR


    VB:
    Sub LOOKUP_3() 
         
        Range("A9").Formula = "=LOOKUP(2,1/(('" & ActiveSheet.Name & "'!C11:C52<>""Total"")*('" & ActiveSheet.Name & "'!AO11:AO52>=1%)),'" & ActiveSheet.Name & "'!C11:C52)" 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,286

    Re: VBA code to get worksheet name

    Kurt

    please use Code Tags as you agreed to in the Forum Rules - there is a further explanation on the page that you enter your ustion/ answers.
    Last edited by royUK; July 8th, 2006 at 15:11.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: VBA code to get worksheet name

    I found some code here from ozgrid.com

    Can someone help me put these together?
    There intructions on its use on the page you got the code from.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 5
    Last Post: October 3rd, 2006, 14:41
  2. Specify the Appropriate Worksheet Code
    By getty7 in forum EXCEL HELP
    Replies: 11
    Last Post: September 13th, 2006, 14:23
  3. Replies: 1
    Last Post: July 12th, 2006, 19:14
  4. Does a worksheet or workbook have code
    By h in forum EXCEL HELP
    Replies: 3
    Last Post: February 12th, 2006, 01:04
  5. Writing code to a worksheet code module
    By Derk in forum EXCEL HELP
    Replies: 14
    Last Post: October 29th, 2004, 22:49

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