[Solved] Formulas: refering to worksheet names in forumlas

  • Question:

    I would like to build the following formula in a VBA macro:

    =IF('02 OCT'!D2="""","""",'02 Oct'!D2)"

    Basically this formula will look at the contents of cell D2 on the worksheet called 02 Oct. If nothing is there, it returns nothing; if a value is there, it returns the value.

    My problem is that in building the formula in VBA the name of the worksheet will change over time as I run the macro. How do I build the formula with a variable that will enter in the name of the worksheet?



  • Try something like this (of course I have no clue where you're putting the formula or where you're getting the sheet name so you'll have to change the code to meet your needs):

    Dim SheetName As String
    SheetName = ActiveSheet.Name
    Sheets("Working").Range("A1").Formula = _
        "IF('" & SheetName & "'!D2="""","""",'" & _
        SheetName & "'!D2)"


    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • I do hope this is OK and that offers flexibility for you too:
    Post back if you need a little extra .

    Kindest Possible regards

    Jack in the UK


    Sub Jack_OzGrid()
    Dim twb As Workbook
    Dim myActiveSheet As Worksheet
    Dim mySource As Range
    Dim myDestination As Range
    Dim myName As String
    Set twb = ThisWorkbook
    Set myActiveSheet = ThisWorkbook.ActiveSheet 'Sheets("Oct02")
    Set mySource = Application.InputBox( _
    prompt:="Please Select Source Cell IE D2", Type:=8)
    Set myDestination = Application.InputBox( _
    prompt:="Please Select a Destination Cell IE A1", Type:=8)
    myName = myActiveSheet.Name
    If mySource.Value = "" Then
    myDestination = "Your Message or Formula is Empty " _
    & myName
    ' *** or if you need to return blank if D2 Empty use this code
    ' mydestination = ""
    End If
    If mySource.Value <> "" Then
    myDestination = "Your Message or formula if populated " _
    & myName
    End If
    Call myKillVar
    End Sub

    Sub myKillVar()
    ' Code if needed !!!
    Set twb = ThisWorkbook
    Set myActiveSheet = Nothing
    Set mySource = Nothing
    Set myDestination = Nothing
    ' Some code [if needed]
    End Sub

Participate now!

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