VBA PivotTable Blues

RedMonkey

New Member
Joined
Apr 27, 2011
Messages
45
I've been peering in the forums for some time and have thankfully learned quite a bit over the last month. At this point I seem to have hit an intellectual wall. And here is a wall of information to go with it.

I have a daily process that I can basically do in my sleep and I'm using it to learn how to automate every process but the data importing. (Too many security hurdles to remove a 2 minute process anyway). Then I can go prove this is good idea for the real big things I get to work on.

I've pickled up automated emails, cutting, pasting, formatting, etc and now I am at the final hurdle, a basic PivotTable.

Here is the data range and resulting table I generate from it, there are other steps that reference data not used so ignore the unused columns: A-F is data culled from a huge autofilter-inputbox process that saved as a value. H-K is the bone simple PivotTable.

Excel Workbook
ABCDEFGHIJKL
1VendorInvoiceNumberNameAlpha AmountGross PurchaseOrderDue DateCompanyKey Sum of AmountGross
265035LVendor Name 2,680.00 4879278/21/201143101 PurchaseOrderVendorInvoiceNumberDue DateTotal
366000LVendor Name 3,870.00 15193058/21/201139301 039494E8/23/2011154.8
44101946073LVendor Name 1,288.52 15215248/22/201164101 48792765035L8/21/20112680
54101946074LVendor Name 17,977.93 15220568/22/201183326 1501802218199E8/20/2011440.23
6242822LVendor Name 1,962.60 15244638/19/201124703 15048123463700L8/22/20115400
7218075EVendor Name 577.59 15138578/18/201133101 1512666T01300985L8/20/201155638.07
8218220LVendor Name 2,841.53 15253318/20/201183326 1513857218075E8/18/2011577.59
9218222LVendor Name 865.00 15253288/20/201183326 151386374825E8/18/20111463.66
10218221LVendor Name 947.18 15253298/20/201183326 1516933IV00316235L8/19/20112736.27
11218199EVendor Name 440.23 15018028/20/201181201 151928945204L8/19/20113264.59
1271811021LVendor Name 413.92 15232428/22/201132490 151930566000L8/21/20113870
1371811419LVendor Name 670.02 15207588/22/201181201 152035771811420L8/22/20119386.93
1471811420LVendor Name 9,386.93 15203578/22/201162101 1520533IV00316699L8/20/2011578.25
153463700LVendor Name 5,400.00 15048128/22/201132493 152075871811419L8/22/2011670.02
1639494EVendor Name 154.80 08/23/201135201 15215244101946073L8/22/20111288.52
1745204LVendor Name 3,264.59 15192898/19/201171606 15218106691L8/18/2011325.71
1845262LVendor Name 30,316.18 15224788/20/201171104 15220564101946074L8/22/201117977.93
1945260LVendor Name 5,009.41 15238928/20/201185105 152247845262L8/20/201130316.18
2045257LVendor Name 1,290.78 15238938/20/201185105 1522669300000115582L8/20/20116575.98
216691LVendor Name 325.71 15218108/18/201183415 1523067147659L8/19/201116841
22IV00316235LVendor Name 2,736.27 15169338/19/201183415 152324271811021L8/22/2011413.92
23IV00316699LVendor Name 578.25 15205338/20/201132490 152389245260L8/20/20115009.41
24IV00316718LVendor Name 17,540.57 15260418/20/201185105 152389345257L8/20/20111290.78
25300000115582LVendor Name 6,575.98 15226698/20/201183421 1524463242822L8/19/20111962.6
26T01300985LVendor Name 55,638.07 15126668/20/201132490 1525328218222L8/20/2011865
27T01301067LVendor Name 29,540.89 15254158/20/201171606 1525329218221L8/20/2011947.18
28147659LVendor Name 16,841.00 15230678/19/201190100 1525331218220L8/20/20112841.53
2974825EVendor Name 1,463.66 15138638/18/201133101 1525415T01301067L8/20/201129540.89
30 1526041IV00316718L8/20/201117540.57
Inv Summarized
Excel 2007

Normally there is a greater concentration but this limited sample doesn't show it.

I've done recording of the process, I have my 2007 VBA book and my 2003 VBA book which actually helped due to pivot code structures having not changed with the new version.

So I got real clever and I am to this point after using DIMs and pulling data from a macro recording, the red text has been my bane :
Rich (BB code):
Sub DDA()
'
'Formatting and Inital PivotTable Macro
'
'
'Change Col D to Funds and other Columns to Simple Number
    Columns("D:D").Style.NumberFormat = "###,###.00"
'Change imported text to simple number via Text to Columns, Default Parameteres are removed
    Range("F:F, N:N").NumberFormat = "0"
'Change three consecutive columns to short date format
    Range("I:K").NumberFormat = "m/dd/yyyy"
'Autofilter InputBox for Due date range for Advanced Filter
    Dim Filtercriteria
    Range("A:N").Select
    Selection.AutoFilter
    FilterCriteria1 = InputBox(Prompt:="What is the Start Date?", Title:="Business Date Before Today's Date", Default:="")
    FilterCriteria2 = InputBox(Prompt:="What is the End Date?", Title:="5 Calendar Days After Today's Date", Default:="")
    Selection.AutoFilter Field:=10, Criteria1:=">=" & FilterCriteria1, Criteria2:="<=" & FilterCriteria2
'Select the needed columns, pastes the copied data where needed and format it
'This is from a recording and needs to be edited down
    Sheets("Inv Original").Select
    Range("C:C,B:B,D:D,F:F,J:J,N:N").Select
    Selection.EntireColumn.Copy _
    Destination:=Sheets("Inv Summary").Range("A1")
    Sheets("Inv Summary").Select
    Range("A:G").Select
    With Selection.EntireColumn.AutoFit
    End With
'Pivotable Procedure DIMs
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("Inv Summary")
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
'Data Input Area and final steps
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange)
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(1, FinalCol + 2), TableName:="Summary Data")
    PT.ManualUpdate = True
' Set up the row & column fields and finalize
   PT.AddFields RowFields:=Array("PurchaseOrder", "VendorInvoiceNumber", "Due Date")
    With PT.PivotFields("AmountGross")
        .Orientation = z1Datafield
        .Function = x1Sum
        .Position = 1
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    With ActiveSheet.PivotTables("Summary Data")
        .ColumnGrand = False
        .RowGrand = False
    End With
'Pull PO PivotTable, create pull list for Crystal Reports
'Format PO Original and copy selected columns to PO Summary
End Sub
I keep getting 1004 errors, currently "Unable to set Function Class" and the fact that the older presentation of pivottables does not help me get past this.

I recorded the process again and tried to showhorn that in so I can trim it later anbd I get an invalid call. Even though I just did it with the data sitting there and the cursor in the same spot.:
Rich (BB code):
Range("H2").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Inv Summary!R1C1:R64C6", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Inv Summary!R2C8", TableName:="PivotTable10", _
        DefaultVersion:=xlPivotTableVersion12
    Sheets("Inv Summary").Select
    Cells(2, 8).Select
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("VendorInvoiceNumber")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("PurchaseOrder")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Due Date")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
        "PivotTable10").PivotFields("AmountGross"), "Sum of AmountGross", xlSum
End Sub
After 2 days I'm stumped, I've compressed alot of things and get this object based scripting but I cannot even do this with a simple recording. What really gets me is any example I see assumes I'm opening things on a new worksheet or doing things beyond this truly simple process

What obvious issue am I missing?

I'm doing this at home so I will not be getting the emails about responses since that is tied to work (Cant upload anything or use the html helper there) so any thanks or further discussion will not be until tomorrow.

Thanks in advance.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
The code you posted has some typos- not sure if it was this way in your code or just in pasting it to the Board.

Replace this...
Rich (BB code):
   With PT.PivotFields("AmountGross")
        .Orientation = z1DataField
        .Function = x1Sum
        .Position = 1
   PT.ManualUpdate = False

With this...
Rich (BB code):
   With PT.PivotFields("AmountGross")
        .Orientation = xlDataField   
        .Function = xlSum
        .Position = 1
   End With    
   PT.ManualUpdate = False

I haven't tried to test the rest of your code as that is time-consuming to create your starting setup.

If you aren't already doing so, try compiling your code first before running - that will allow you to catch and fix errors like those above prior to runtime.
 
Last edited:
Upvote 0
In my frustration I used a saved code from another save that did not have the End With and the z for x. Otherwise this is a classic "need a new set of eyes after looking at this too long" answer since I was using a 1 instead of l and I am very grateful for the catch.

I will also practice a bit more brevity in what raw numbers and code I post.

So now it runs! And this is the result, no data at all even though all the filter tags is shows up with are showing that all data was captured in the cache.

<TABLE style="WIDTH: 319pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=424><COLGROUP><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><COL style="WIDTH: 123pt; mso-width-source: userset; mso-width-alt: 5997" width=164><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 106pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl69 height=17 width=141>Sum of AmountGross</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 123pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl68 width=164></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl68 width=82></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=37></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl69 height=17>PurchaseOrder</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl69>VendorInvoiceNumber</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl69>Due Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: black 0.5pt solid" class=xl70>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl66 height=17></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl71></TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67></TD></TR></TBODY></TABLE>



EDIT: And nevermind, I did not understand when to code for removing subtotals and grand totals, I have it working just fine now in the format I need.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

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