title: Excel VBA Using pivot table data in another workbook
body: <p>The following Sub asks the user to select a generic template to open, then asks the user to select a source file (to populate the template). The source file is contains a number of worksheets and pivot tables. The sub then selects data from a pivot table and copies it into the template. 
  I need the source file to be a variable vs. a hard coded pivot table source bc this title changes based on the users selection.</p>
  
  <p>Issue 1: when the data is copied it only shows REF! instead of the actual data (even when the data is present). </p>
  
  <pre><code>'Open Generic Report to populate with data
  Dim GenericFolderLocation As String
  MsgBox "Please select the generic porfolio template..."
  GenericFolderLocation = "C:\Users\user.name\Desktop"
     ChDrive GenericFolderLocation
  
    SelectedFile = Application.GetOpenFilename( _
          fileFilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=False)
  
          Workbooks.Open (SelectedFile)
          Set test = ActiveWorkbook
  
  Dim SourceFolderLocation As String
  Dim FileName As String
  Dim SourceFile As String
  MsgBox "Please select the data source file..."
  SourceFolderLocation = "C\Users\user.name\Desktop"
  ChDrive SourceFolderLocation
  SourceFile = Application.GetOpenFilename( _
      fileFilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=False)
  
      Workbooks.Open (SourceFile)
      Set wkbk = ActiveWorkbook
  
      test.Activate
  
      'Test1
      'Select empty cell in Chart template 
      Range("C28").Select
      'Populate with pivot table data from sourceFile
      ActiveCell.FormulaR1C1 = _
          "=GETPIVOTDATA(""  Value"",'[wkbk]ActCost_PIVOT'!R3C1,""Team"",""Field1"",""Row Descrption"",""Row1"",""Type"",""DataPoint1"")"
  
      'Repeat for next cell
      Range("C27").Select
      ActiveCell.FormulaR1C1 = _
          "=GETPIVOTDATA(""  Value"",'[wkbk]CRActCost_PIVOT'!R3C1,""Team"",""Field1"",""Row Descrption"",""Row1"",""Type"",""DataPoint2"")"
  </code></pre>
  
  <p>[Resolved] Issue 2. Similar to issue 1 I would like to make things like "TypeName" or "TeamName" variables. Can I just declare them outside the Sub like this- </p>
  
  <pre><code>    Dim TeamName As String
  
      Sub()
  
      TeamName = "Tigers"
  
      End Sub
  </code></pre>
  
  <p>Thanks for your help!</p>