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.
Issue 1: when the data is copied it only shows REF! instead of the actual data (even when the data is present).
'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"")"
[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-
Dim TeamName As String
Sub()
TeamName = "Tigers"
End Sub
Thanks for your help!
Best Answer
There is no problem to define global variables (outside any sub/function) in VBA. What you propose for
TeamName
would be fine (write the variable define at the top of the given module). You can use this variable afterwards inside this module without any problem. For example by doing:As you can see, you have to take care of the quotes in case of being required (to escape them). A trickto avoid confusions is putting them completely appart (""""), as shown above.
Other issue I wanted to highlight is that you can access all the contents of any
PivotTable
directly via VBA (you don't need the formula in your question):From the
pivotTable
variable you have full access to all the contents of the given PivotTable. This represents the kind of advantages of VBA over Excel, which you should maximise (if you continue relying on formulae, why using VBA at all?).I guess that they were implicit in my aforementioned answer, but just to make sure, here you have my answers to your specific questions:
&
s. In case of having quotes you have to escape them as suggested above.TeamName
is right and you should emulate it for any other (string) variable you want to use globally. Nevertheless, bear in mind that having too many global variables is not a good coding practice; intend to rely on local variables as much as possible.