How to insert variable value into getpivotdata formula using excel vba macro

Question

So I've learned pivot tables are tricky, so are string variable syntax, and so is the use of quotations in VBA. I'm trying (and failing) to use all three!

I am trying to collect one piece of data from a very large/complex pivot table for a large number of job numbers. I want to select a job number in A1, and have the pivot table formula automatically update with the selected job number to return a number result.

I am currently having two problems: 1) the syntax to get the job number in quotations for the 'getpivotdata' formula isn't working and 2)it is dropping off the zeros in the job number when it executes the code.

My code is listed below, when I execute it I am hoping to see this formula populate in A2:

=GETPIVOTDATA("Part Number",' Parts Status '!$A$8,"CHAR_FIELD3","11-008","MATERIAL STATUS MASTER","Avail")

but instead get:

=GETPIVOTDATA("Part Number",' Parts Status '!$A$8,"CHAR_FIELD3",11-8,"MATERIAL STATUS MASTER","Avail")

My code is here

Sub Macro1()

Dim jobnumber As String

jobnumber = Worksheets("Macros test").Cells(1, "A").Value

Sheets("Macros test").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Part Number"",' Parts Status '!R8C1,""CHAR_FIELD3""," & jobnumber & ",""MATERIAL STATUS MASTER"",""Avail"")"

Answer 1

If you need the quotes there, change it to this:

"=GETPIVOTDATA(""Part Number"",' Parts Status '!R8C1,""CHAR_FIELD3"",""" & jobnumber & """,""MATERIAL STATUS MASTER"",""Avail"")"

Also, you can add a temporary msgbox after setting jobnumber to make sure it is what you think it is:

MsgBox(jobnumber)

Answer 2

I've always used the Chr() function to deal with quotes in VB. Double quote is Chr(34) and single quote is Chr(39). The problem you experienced was that while jobnumber is defined as a string you didn't have the double quotes around it. The result is Excel removed the leading zeros on the formula 11-008, which Excel saw as Eleven minus eight. Adding the double quotes (Chr(34)) around jobnumber solved the problem.

I always break these long strings into smaller pieces so that I can see what I've typed. The code below uses a little private function to build your pivot string.

Public Sub PivotTest()

    Dim jobnumber As String

    jobnumber = Worksheets("Macros test").Cells(1, "A").Value

    Sheets("Macros test").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = BuildPivotString(jobnumber)

End Sub

Private Function BuildPivotString(ByRef jobNum As Variant) As String
    Dim retVal As String

    retVal = "=GETPIVOTDATA("
    retVal = retVal & Chr(34) & "Part Number" & Chr(34)
    retVal = retVal & ",' Parts Status '!R8C1,"
    retVal = retVal & Chr(34) & "CHAR_FIELD3" & Chr(34) & ","
    retVal = retVal & Chr(34) & jobNum & Chr(34) & ","
    retVal = retVal & Chr(34) & "MATERIAL STATUS MASTER" & Chr(34) & ","
    retVal = retVal & Chr(34) & "Avail" & Chr(34) & ")"

    BuildPivotString = retVal
End Function