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 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 :
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.:
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.
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 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
ABB2XL Click to copy to clipboard. Alt+click for values & formatting only."> | B | C | D | E | F | G | H | I | J | K | L | |||
1 | VendorInvoiceNumber | NameAlpha | AmountGross | PurchaseOrder | Due Date | CompanyKey | Sum of AmountGross | |||||||
2 | 65035L | Vendor Name | 2,680.00 | 487927 | 8/21/2011 | 43101 | PurchaseOrder | VendorInvoiceNumber | Due Date | Total | ||||
3 | 66000L | Vendor Name | 3,870.00 | 1519305 | 8/21/2011 | 39301 | 0 | 39494E | 8/23/2011 | 154.8 | ||||
4 | 4101946073L | Vendor Name | 1,288.52 | 1521524 | 8/22/2011 | 64101 | 487927 | 65035L | 8/21/2011 | 2680 | ||||
5 | 4101946074L | Vendor Name | 17,977.93 | 1522056 | 8/22/2011 | 83326 | 1501802 | 218199E | 8/20/2011 | 440.23 | ||||
6 | 242822L | Vendor Name | 1,962.60 | 1524463 | 8/19/2011 | 24703 | 1504812 | 3463700L | 8/22/2011 | 5400 | ||||
7 | 218075E | Vendor Name | 577.59 | 1513857 | 8/18/2011 | 33101 | 1512666 | T01300985L | 8/20/2011 | 55638.07 | ||||
8 | 218220L | Vendor Name | 2,841.53 | 1525331 | 8/20/2011 | 83326 | 1513857 | 218075E | 8/18/2011 | 577.59 | ||||
9 | 218222L | Vendor Name | 865.00 | 1525328 | 8/20/2011 | 83326 | 1513863 | 74825E | 8/18/2011 | 1463.66 | ||||
10 | 218221L | Vendor Name | 947.18 | 1525329 | 8/20/2011 | 83326 | 1516933 | IV00316235L | 8/19/2011 | 2736.27 | ||||
11 | 218199E | Vendor Name | 440.23 | 1501802 | 8/20/2011 | 81201 | 1519289 | 45204L | 8/19/2011 | 3264.59 | ||||
12 | 71811021L | Vendor Name | 413.92 | 1523242 | 8/22/2011 | 32490 | 1519305 | 66000L | 8/21/2011 | 3870 | ||||
13 | 71811419L | Vendor Name | 670.02 | 1520758 | 8/22/2011 | 81201 | 1520357 | 71811420L | 8/22/2011 | 9386.93 | ||||
14 | 71811420L | Vendor Name | 9,386.93 | 1520357 | 8/22/2011 | 62101 | 1520533 | IV00316699L | 8/20/2011 | 578.25 | ||||
15 | 3463700L | Vendor Name | 5,400.00 | 1504812 | 8/22/2011 | 32493 | 1520758 | 71811419L | 8/22/2011 | 670.02 | ||||
16 | 39494E | Vendor Name | 154.80 | 0 | 8/23/2011 | 35201 | 1521524 | 4101946073L | 8/22/2011 | 1288.52 | ||||
17 | 45204L | Vendor Name | 3,264.59 | 1519289 | 8/19/2011 | 71606 | 1521810 | 6691L | 8/18/2011 | 325.71 | ||||
18 | 45262L | Vendor Name | 30,316.18 | 1522478 | 8/20/2011 | 71104 | 1522056 | 4101946074L | 8/22/2011 | 17977.93 | ||||
19 | 45260L | Vendor Name | 5,009.41 | 1523892 | 8/20/2011 | 85105 | 1522478 | 45262L | 8/20/2011 | 30316.18 | ||||
20 | 45257L | Vendor Name | 1,290.78 | 1523893 | 8/20/2011 | 85105 | 1522669 | 300000115582L | 8/20/2011 | 6575.98 | ||||
21 | 6691L | Vendor Name | 325.71 | 1521810 | 8/18/2011 | 83415 | 1523067 | 147659L | 8/19/2011 | 16841 | ||||
22 | IV00316235L | Vendor Name | 2,736.27 | 1516933 | 8/19/2011 | 83415 | 1523242 | 71811021L | 8/22/2011 | 413.92 | ||||
23 | IV00316699L | Vendor Name | 578.25 | 1520533 | 8/20/2011 | 32490 | 1523892 | 45260L | 8/20/2011 | 5009.41 | ||||
24 | IV00316718L | Vendor Name | 17,540.57 | 1526041 | 8/20/2011 | 85105 | 1523893 | 45257L | 8/20/2011 | 1290.78 | ||||
25 | 300000115582L | Vendor Name | 6,575.98 | 1522669 | 8/20/2011 | 83421 | 1524463 | 242822L | 8/19/2011 | 1962.6 | ||||
26 | T01300985L | Vendor Name | 55,638.07 | 1512666 | 8/20/2011 | 32490 | 1525328 | 218222L | 8/20/2011 | 865 | ||||
27 | T01301067L | Vendor Name | 29,540.89 | 1525415 | 8/20/2011 | 71606 | 1525329 | 218221L | 8/20/2011 | 947.18 | ||||
28 | 147659L | Vendor Name | 16,841.00 | 1523067 | 8/19/2011 | 90100 | 1525331 | 218220L | 8/20/2011 | 2841.53 | ||||
29 | 74825E | Vendor Name | 1,463.66 | 1513863 | 8/18/2011 | 33101 | 1525415 | T01301067L | 8/20/2011 | 29540.89 | ||||
30 | 1526041 | IV00316718L | 8/20/2011 | 17540.57 | ||||||||||
Inv Summarized |
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 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
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.