Macro: Pivot table from another Tab

C

CVBASIMON

New Member
Joined
May 30, 2012
Messages
30
I am trying to create a pivot table, I would like the table on a diffrent tab then that of the data. Below is the code I am using, it is running all the way through not erroring out but my screen comes up as blank. Can some one tell me why my code is not pulling in the correct data or why the tab is blank?

HTML: 
'Creating Pivot Table
    Sub Create_Pivot_Table()
    
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Pivot_Table"
    Range("A1").Select
    
        Dim WSD As Worksheet
        Dim PL As Worksheet
        Dim PTCache As PivotCache
        Dim PT As PivotTable
        Dim PRange As Range
        Dim FinalRow As Long
        Set WSD = Worksheets("Pivot_Table")
        Set PL = Worksheets("AP_Parking_Lot")
    'Delete any Prior Pivot Tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
    'Define Input area and set up a Pivot Cache
    Sheets("AP_Parking_Lot").Select
    FinalRow = PL.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = PL.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    Set PRange = PL.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
    'Create the Pivot Table from the Pivot Cache
    Sheets("Pivot_Table").Select
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), TableName:="PivotTable1")
    'Turn off updating while building the table
    PT.ManualUpdate = True
    'set up the row & Column fields
    PT.AddFields RowFields:=Array("Client", "Prof. Center", "Vendor", "PO Number", "Aging Days", "Document Date", "Posting Date", "Reference", "Text", "Document Number", "Invoice Number", "WBS", "GL ACCT")
    'set up the data fields
    With PT.PivotFields("Inv. Amount")
        .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
    'Calc the pivot Table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    'Format Pivot Table
    PT.ShowTableStyleRowStripes = True
    With PT
        .ColumnGrand = False
        .RowGrand = False
        .RepeatAllLabels xlRepeatLabels
    End With
    WSD.Activate
    Range("A2").Select
    
        
    Application.EnableEvents = True
    
    Application.ScreenUpdating = True

End Sub
 
Sort by date Sort by votes
Update: Pivot was always there unfortunatley it was in column "AM" I have since taken out "FinalCol +" and left it at .Cells(2,2).
Code:
<HTML>
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, 2), TableName:="PivotTable1")
'Turn off updating while building the table
<HTML>

I am left with one remaining issue: my datafield is blank does anyone see something wrong with the code below??????

Code:
<HTML>
With PT.PivotFields("Inv. Amount")
.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End With
<HTML>
 
Upvote 0
For anyone who cares the finished code:


Code:
HTML: 
'Creating Pivot Table
    Sub Create_Pivot_Table()
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Pivot_Table"
    Range("A1").Select
    
        Dim WSD As Worksheet
        Dim PL As Worksheet
        Dim PTCache As PivotCache
        Dim PT As PivotTable
        Dim PRange As Range
        Dim FinalRow As Long
        Set WSD = Worksheets("Pivot_Table")
        Set PL = Worksheets("AP_Parking_Lot")
    'Delete any Prior Pivot Tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
    'Define Input area and set up a Pivot Cache
    Sheets("AP_Parking_Lot").Select
    FinalRow = PL.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = PL.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    Set PRange = PL.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
    'Create the Pivot Table from the Pivot Cache
    Sheets("Pivot_Table").Select
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, 2), TableName:="PivotTable1")
    'Turn off updating while building the table
    PT.ManualUpdate = True
    'set up the row & Column fields
    PT.AddFields RowFields:=Array("Client", "Prof. Center", "Vendor", "PO Number", "Aging Days", "Document Date", "Posting Date", "Reference", "Text", "Document Number", "Invoice Number", "WBS", "GL ACCT")
    'set up the data fields
    With PT.PivotFields("Inv. Amount")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        .NumberFormat = "#,##0"
        .Name = "Amount"
    End With
    'Calc the pivot Table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    'Format Pivot Table
    PT.ShowTableStyleRowStripes = True
    PT.TableStyle2 = "Pivotstylemedium10"
    With PT
        .ColumnGrand = False
        .RowGrand = False
        .RepeatAllLabels xlRepeatLabels
    End With
    PT.PivotFields("Prof. Center").Subtotals(1) = False
    PT.PivotFields("Vendor").Subtotals(1) = False
    PT.PivotFields("PO Number").Subtotals(1) = False
    PT.PivotFields("Aging Days").Subtotals(1) = False
    PT.PivotFields("Document Date").Subtotals(1) = False
    PT.PivotFields("Posting Date").Subtotals(1) = False
    PT.PivotFields("Reference").Subtotals(1) = False
    PT.PivotFields("Text").Subtotals(1) = False
    PT.PivotFields("Document Number").Subtotals(1) = False
    PT.PivotFields("Invoice Number").Subtotals(1) = False
    PT.PivotFields("WBS").Subtotals(1) = False
    PT.PivotFields("GL ACCT").Subtotals(1) = False
    PT.ManualUpdate = False
    
    
    
        
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Help with Pivot Table Macro
Replies
0
Views
190
SCPbrito
S
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
489
Jette3173
J
M
  • Question Question
VBA to match number of table rows to pivot rows?
Replies
6
Views
300
Alex Blakenburg
A
H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
991
howard
H
N
  • Question Question
Pivot graph data when creating a new tab from a template.
Replies
0
Views
290
Nippyzap
N

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top