ComboBox.Value reference to worksheet name and reference pivot table from another workbook

Hello everyone, I have been looking everywhere trying to find a solution to my vba macro and have yet to find something that will work. So what I am trying to do is run userform containing two comboboxes (which I have put together and seems to be working fine), that after completion of the userform will begin another macro containing code attempting to reference a pivot table on a separate workbook (the workbook name is variable based on the userform filled out before (i.e. January2014.xlsx), which I am having difficulty coding correctly to make the reference work) to draw data from it. I am fairly new to coding so this complex idea of jumping around workbooks using pivot tables and using variables based on the userform to reference is very confusing. Below is the macro referenced after the user form is completed. The embedded DATE function is my attempt at making the pivot table reference change with the combobox choice (date function for april works with DATE(2014,4,1)).

Any help would be greatly appreciated, as I have been stuck on this for a while. Thanks!


ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Sum of A"",'[UserForm1.MonthComboBox.Value & UserForm1.YearComboBox.Value]by Channel'!R4C1,""Month"",DATE(UserForm1.YearComboBox.Value,Monthnm(UserForm1.MonthComboBox.Value),1))"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Sum of B"",'[UserForm1.MonthComboBox.Value & UserForm1.YearComboBox.Value]by Channel'!R4C1,""Month"",DATE(UserForm1.YearComboBox.Value,Monthnm(UserForm1.MonthComboBox.Value),1))"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Sum of Ap"",'[UserForm1.MonthComboBox.Value & UserForm1.YearComboBox.Value]by Channel'!R4C1,""Month"",DATE(UserForm1.YearComboBox.Value,Monthnm(UserForm1.MonthComboBox.Value),1))"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Sorry about that, I will do that in the future. All but one thing worked... the Monthnm function did not come out as desired? Maybe there is a different function out there I need to use? Need to convert a word form of month (i.e. March) to the number of month (i.e. 3). Do you know how I could make that work?

Thanks!

Thank you so much for your help! The first formula did not work for me (not sure why), and so I just decided on the ListIndex one. When I used the ListIndex, it always came back minus 1 of what I wanted so I just included +1 after and it works fine. So the ListIndex must count the first one in the list as zero meaning January = 0. Again I really appreciate all your help!