[Solved] Formulas: refering to worksheet names in forumlas

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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!