Results 1 to 2 of 2

Thread: Pivot Table Macro Problem

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    22

    Pivot Table Macro Problem

    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??????????????????????

  2. #2
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: Pivot Table Macro Problem

    posted with code tags / indented for easier read:

    VB Code:
    1. ' Macro to load Pivot Table 3 from the reporting data sheet
    2. ' David Taverner 13.4.06
    3. Sub Macro_Four_MainPivot()
    4.    
    5.     Dim LastRow As Long
    6.     Dim LastCol As Long
    7.     Dim MyPivot As PivotTable
    8.     Dim rngSource As Range
    9.    
    10.     'Get a reference to the Pivot Table
    11.     Set MyPivot = ThisWorkbook.Worksheets("Statistical analysis").PivotTables(3)
    12.    
    13.    
    14.     ' Error-handling is here in case there is not any
    15.     ' data in the worksheet
    16.    
    17.     On Error Resume Next
    18.    
    19.     ' Find the last real row from worksheet 'reporting'
    20.     LastRow = 0
    21.     LastCol = 0
    22.     LastRow = ThisWorkbook.Worksheets("reporting").Cells.Find(What:="*", _
    23.         SearchDirection:=xlPrevious, _
    24.         SearchOrder:=xlByRows).Row
    25.     MsgBox "Source Data Rows" & LastRow
    26.     ' Find the last real column from worksheet 'reporting'
    27.    
    28.     LastCol = ThisWorkbook.Worksheets("reporting").Cells.Find(What:="*", _
    29.         SearchDirection:=xlPrevious, _
    30.         SearchOrder:=xlByColumns).Column
    31.     MsgBox "Source Data Columns" & LastCol
    32.     'End With
    33.    
    34.    
    35.     'Get a reference to the source data table
    36.     With ThisWorkbook.Worksheets("Reporting")
    37.         Set rngSource = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
    38.     End With
    39.     With MyPivot
    40.        
    41.         'Update the Source data of the PT
    42.         .SourceData = "Reporting!" & rngSource.Address(ReferenceStyle:=xlR1C1)
    43.        
    44.         'Refresh the dat in the PT
    45.         .RefreshTable
    46.        
    47.     End With
    48.     ActiveSheet.PivotTables("PivotTable3").RefreshTable
    49.    
    50.     LastRow = 0 ' clear values
    51.     LastCol = 0
    52.    
    53.     'Clear Object Variables
    54.     Set rngSource = Nothing
    55.     Set MyPivot = Nothing
    56. End Sub
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width