I have two worksheets in the same workbook: "PIR-DT MTH" and "PIR-DT CAT"
In "PIR-DT MTH", in cell "E3" there is a string for an range value, for example "C4:L21"
In "PIR-DT CAT" there is a Pivot Table called "PIR1DTCAT" with Row Field "PIR-DTCAT" and Data Field "IR1 DT TIME" (there is no Column Field). The Table uses the range value mentioned above to capture the source data for the table
The range value in "E3" on "PIR-DT MTH" changes by different triggers, the details of which are not important
However, when the value in that particular cell changes, the Pivot Table in worksheet "PIR-DT CAT" must also update.
So I have a macro, which is supposed to capture the new range value and update the pivot table.
I tried the very same code on a sample workbook and it works just fine, but when I test on the actual workbook, I get an error:
The call to the function is as follows:
Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3")
The actual function is as follows (note I put the values that are assigned to the internal variables in red for your reference):
Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _
ObjWkSheetName As String, InfoType As String, _
IRNumber As String, RangeInfoCell As String)
Dim myexcel As Object
Dim myworkbook As Object
Dim sourceworksheet As Object
Dim objworksheet As Object
Dim PivotTableName As String
'Dim PivotRowField As String
'Dim PivotDataField As String
Dim PivotSourceData As String
Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application
Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook
Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet
Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet
PivotTableName = "PIR" & IRNumber & InfoType '"PIR1DTCAT"
PivotRowField = "PIR" & IRNumber & "-" & InfoType '"PIR-DTCAT"
PivotDataField = "IR" & IRNumber & " DT TIME" '"IR1 DT TIME"
If sourceworksheet.Range(RangeInfoCell).Value = "None" Then '"C4:L21"
MsgBox "You have to delete the Pivot Table"
Else
PivotSourceData = "'" & SourceWkSheetName & "'!" & sourceworksheet.Range(RangeInfoCell).Value '"PIR-DT MTH'!C4:L21"
objworksheet.PivotTables(PivotTableName).PivotTableWizard SourceType:=xlDatabase, SourceData:= _
PivotSourceData 'THIS IS THE LINE WHERE I GET THE ERROR
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
End If
End Sub
The error which occur on the line indicated above states:
The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.
I don't understand what is happening
Now all of this can be avoided, if I could only figure out how to instead of putting the range reference like - 'PIR-DT MTH'!$C$4:$L$32 for the Pivot Table, put the value of the cell where the range reference string is stored - =TEXT('PIR-DT MTH'!E3
When I tried that I got an error Reference is not valid
Bookmarks