Announcement

Collapse
No announcement yet.

VBA code to get worksheet name

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

  • 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
    Last edited by royUK; July 8th, 2006, 15:08.

  • #2
    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
    Last edited by royUK; July 8th, 2006, 15:08.

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            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, 15:11.
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            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.

            Comment


            • #7
              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.

              Comment

              Working...
              X