I have two worksheets in the same workbook: "PIR-DT MTH" and "PIR-D
CAT"
In "PIR-DT MTH", in cell "E3" there is a string for an range value, fo
example "C4:L21"
In "PIR-DT CAT" there is a Pivot Table called "PIR1DTCAT" with Ro
Field "PIR-DTCAT" and Data Field "IR1 DT TIME" (there is no Colum
Field). The Table uses the range value mentioned above to capture th
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 Pivo
Table in worksheet "PIR-DT CAT" must also update.
So I have a macro, which is supposed to capture the new range value an
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 ar
assigned to the internal variables in red for your reference):

Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName A
String, _
ObjWkSheetName As String, InfoType A
String, _
IRNumber As String, RangeInfoCell A
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 activ
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 t
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" The
'"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).PivotTabl eWizar
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. I
you are changing the name of a PivotTable field, you must type a ne
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 t
instead of putting the range reference like - 'PIR-DT MTH'!$C$4:$L$3
for the Pivot Table, put the value of the cell where the rang
reference string is stored - =TEXT('PIR-DT MTH'!E3;)
When I tried that I got an error Reference is not vali

--
suzette
-----------------------------------------------------------------------
suzetter's Profile: http://www.excelforum.com/member.php...nfo&userid=707
View this thread: http://www.excelforum.com/showthread.php?threadid=38935