Hi!

I have one sheet called "Date Detail" which has 6 columns:

date month day Year # Emails Custodian


I need to create 2 pivot tables (one in sheet called "Yearly Summary"
and the other one in "Monthly Summary") programatically.

"Yearly Summary" only uses the columns Year, # Emails and Custodian.

Year is in the rows, custodian is in the columns and # emails is in
the center.


The problem is that "Date Detail" gets populated from SQL Server and
will have variable number of rows depending on which project' I run.


Also This is run off a commandbutton in the "selection data" sheet.


My code is as follows:



Sub MacroMainPivot()

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("Yearly Summary").PivotTables(1)

'Insert whatever code you were using to get
'the last row and column numbers here

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

On Error Resume Next

With ws

' Find the last real row

LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'MsgBox "" & LastRow
' Find the last real column

LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'MsgBox "" & LastCol
End With

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

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

'Refresh the dat in the PT
..RefreshTable

End With

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


When I run this code I get a runtime error 9 subscript out of range


at this line:

Set MyPivot = ThisWorkbook.Worksheets("Yearly Summary").PivotTables(1)


I really need help with this.

Thanks in advanced!

Tammy