pivot table using vba problem

[TG]

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

 

[Mike Fogleman]

The secret to refreshing a pivot table with new data is not to address the
table itself, but merely re-define the source data that the PT uses. Use a
named range for the data such as "Database". Go to your PT and use the
Wizard to set the data range to Date Detail!Database. This sets your data
range to a named range. Now all you need to do is to re-define the named
range when new data is imported.

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

'next line will re-define the named range
..Names("Database").RefersTo = "=" & rngSource.Address(1, 1, xlA1, True)

End With

'next line will refresh PTs
ThisWorkbook.RefreshAll
End Sub

Since you have 6 columns of data and 2 PTs that only refer to half of the
data columns you may want to use 2 named ranges, one for the first 3 columns
and a different name for the last 3 columns. You would then use the above
procedure to re-define 2 named ranges.

'Get a reference to the source data table
With ThisWorkbook.Worksheets("Date Detail")

Set rngSource = .Range(.Cells(1, 1), .Cells(LastRow, LastCol - 3))
'next line will re-define the named range
..Names("Database1").RefersTo = "=" & rngSource.Address(1, 1, xlA1, True)
Set rngSource = .Range(.Cells(4, 1), .Cells(LastRow, LastCol))
..Names("Database2").RefersTo = "=" & rngSource.Address(1, 1, xlA1, True)

End With

'next line will refresh PTs
ThisWorkbook.RefreshAll
End Sub

Hope this helps,
Mike F