VBA code to get worksheet name

    Hello Everyone,


    I now have the vba code formula thanks to Domenic.

    Code
    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

    Re: VBA code to get worksheet name


    Hello,


    I found some code here from ozgrid.com


    Can someone help me put these together?

    Code
    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

    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

    Quote

    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

    Re: VBA code to get worksheet name


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



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


End Sub

    Re: VBA code to get worksheet name


    Here are 2 other options you might want to try.


    Code
    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



    Code
    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

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login