Results 1 to 9 of 9

Error on Pivot Table VBA

Threaded View

geng Error on Pivot Table VBA 06-01-2010, 03:00 AM
romperstomper Re: Error on Pivot Table VBA 06-01-2010, 04:38 AM
geng Re: Error on Pivot Table VBA 06-01-2010, 07:40 PM
romperstomper Re: Error on Pivot Table VBA 06-02-2010, 02:18 AM
geng Re: Error on Pivot Table VBA 06-02-2010, 02:31 AM
romperstomper Re: Error on Pivot Table VBA 06-02-2010, 04:13 AM
geng Re: Error on Pivot Table VBA 06-02-2010, 07:10 PM
romperstomper Re: Error on Pivot Table VBA 06-03-2010, 03:33 AM
geng Re: Error on Pivot Table VBA 06-03-2010, 07:34 PM
  1. #1
    Registered User
    Join Date
    05-31-2010
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    88

    Error on Pivot Table VBA

    Hi, I got a sample VBA code form this forum, this is to insert Pivot Table, and I tried modifying it, but it just gave me an error.

    Option Explicit 
    Sub Pivot() 
         ' pivot Macro
        Dim pt As PivotTable 
        Dim strField As String 
        Dim WSD As Worksheet 
        Set WSD = Worksheets("Sheet1") 
        Dim PTOutput As Worksheet 
        Set PTOutput = Worksheets("Pivot Table") 
        Dim PTCache As PivotCache 
        Dim PRange As Range 
         ' Find the last row with data
        Dim finalRow As Long 
        finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row 
         
         ' Find the last column with data
        Dim finalCol As Long 
        finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column 
         
         ' Find the range of the data
        Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol) 
        Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange) 
         ' Create the pivot table
        Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _ 
        TableName:="Pivot") 
         
         ' Set update to manual to avoid recomputation while laying out
        pt.ManualUpdate = True 
         
         ' Set up the row fields
        pt.AddFields RowFields:=Array( _ 
        "Market", "Date", "Category", "Business") 
         ' Set up the data fields
        With pt.PivotFields("Pieces Ordered") 
            .Orientation = xlDataField 
            .Function = xlSum 
            .Position = 1 
        End With 
        With pt.PivotFields("Actual Sales") 
            .Orientation = xlDataField 
            .Function = xlSum 
            .Position = 2 
        End With 
        With pt.PivotFields("Lost Sales") 
            .Orientation = xlDataField 
            .Function = xlSum 
            .Position = 3 
        End With 
         ' Now calc the pivot table
        pt.ManualUpdate = False 
    End Sub
    This line is giving me the error
    Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _ 
    TableName:="Pivot")
    Thanks!!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1