Pivot Table Macro Problem

[djtaverner]

I have the following code that creates pivot table 3 in "Statistical analysis" worksheet from data in "reporting" worksheet:

' Macro to load Pivot Table 3 from the reporting data sheet
' David Taverner 13.4.06
Sub Macro_Four_MainPivot()

Dim LastRow As Long
Dim LastCol As Long
Dim MyPivot As PivotTable
Dim rngSource As Range

'Get a reference to the Pivot Table
Set MyPivot = ThisWorkbook.Worksheets("Statistical analysis").PivotTables(3)


' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

' Find the last real row from worksheet 'reporting'
LastRow = 0
LastCol = 0
LastRow = ThisWorkbook.Worksheets("reporting").Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
MsgBox "Source Data Rows" & LastRow
' Find the last real column from worksheet 'reporting'

LastCol = ThisWorkbook.Worksheets("reporting").Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
MsgBox "Source Data Columns" & LastCol
'End With


'Get a reference to the source data table
With ThisWorkbook.Worksheets("Reporting")
Set rngSource = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With
With MyPivot

'Update the Source data of the PT
.SourceData = "Reporting!" & rngSource.Address(ReferenceStyle:=xlR1C1)

'Refresh the dat in the PT
.RefreshTable

End With
ActiveSheet.PivotTables("PivotTable3").RefreshTable

LastRow = 0 ' clear values
LastCol = 0

'Clear Object Variables
Set rngSource = Nothing
Set MyPivot = Nothing
End Sub

When I go into "Statistical analysis" sheet and right select the pivot table and goto table options > error message "pivot table is not valid"

Any ideas??????????????????????

[Static]

posted with code tags / indented for easier read:

' Macro to load Pivot Table 3 from the reporting data sheet
' David Taverner 13.4.06
Sub Macro_Four_MainPivot()
    
    Dim LastRow As Long
    Dim LastCol As Long
    Dim MyPivot As PivotTable
    Dim rngSource As Range
    
    'Get a reference to the Pivot Table
    Set MyPivot = ThisWorkbook.Worksheets("Statistical analysis").PivotTables(3)
    
    
    ' Error-handling is here in case there is not any
    ' data in the worksheet
    
    On Error Resume Next
    
    ' Find the last real row from worksheet 'reporting'
    LastRow = 0
    LastCol = 0
    LastRow = ThisWorkbook.Worksheets("reporting").Cells.Find(What:="*", _
        SearchDirection:=xlPrevious, _
        SearchOrder:=xlByRows).Row
    MsgBox "Source Data Rows" & LastRow
    ' Find the last real column from worksheet 'reporting'
    
    LastCol = ThisWorkbook.Worksheets("reporting").Cells.Find(What:="*", _
        SearchDirection:=xlPrevious, _
        SearchOrder:=xlByColumns).Column
    MsgBox "Source Data Columns" & LastCol
    'End With
    
    
    'Get a reference to the source data table
    With ThisWorkbook.Worksheets("Reporting")
        Set rngSource = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
    End With
    With MyPivot
        
        'Update the Source data of the PT
        .SourceData = "Reporting!" & rngSource.Address(ReferenceStyle:=xlR1C1)
        
        'Refresh the dat in the PT
        .RefreshTable
        
    End With
    ActiveSheet.PivotTables("PivotTable3").RefreshTable
    
    LastRow = 0 ' clear values
    LastCol = 0
    
    'Clear Object Variables
    Set rngSource = Nothing
    Set MyPivot = Nothing
End Sub