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:

activecell.formular1c1 = _
        "=getpivotdata(""  sum"",'[sourcefile]workbooka_pivot1'!r3c1,""team/branch""," & """" & teamname & """" & ",""row descrption"",""rowname"",""type"",""typename"")"

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):

 dim pivottable as pivottable
 set pivottable = activesheet.pivottables("pivottable name")

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:

  1. making the name of workbook a a variant: i want the file the user selects to be called "sourcefile". this isn't working... not sure why... -> if you have a code working as you want, you can replace a string on it with a variable (for example: "workbooka"), just by insert the variable in the right place and concatenating it with the remaining bits via &s. in case of having quotes you have to escape them as suggested above.
  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 -> the code you propose for 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.

Related Query

More Query from same tag