hi guys
i've managed to solve my biggest problem, showing a pivot table with sub-items
the data i have is data of last week and current week ( i have a date column in my array )
my pivot table right now is showing all the data, but instead i need the first column to count the data of last week ( based on date ) and the last pivot table column show the number of data of current week
[TABLE="width: 500"]
<tbody>[TR]
[TD]random data[/TD]
[TD]more random data[/TD]
[TD]stuff[/TD]
[TD]15/03/2015[/TD]
[/TR]
[TR]
[TD]random data[/TD]
[TD]random[/TD]
[TD]data[/TD]
[TD]10/03/2015[/TD]
[/TR]
[TR]
[TD]random[/TD]
[TD]data[/TD]
[TD]some[/TD]
[TD]23/03/2015[/TD]
[/TR]
[TR]
[TD]some[/TD]
[TD]random[/TD]
[TD]data[/TD]
[TD]23/03/2015[/TD]
[/TR]
[TR]
[TD]again[/TD]
[TD]some[/TD]
[TD]random[/TD]
[TD]18/03/2015[/TD]
[/TR]
[TR]
[TD]and[/TD]
[TD]some[/TD]
[TD]more[/TD]
[TD]19/03/2015[/TD]
[/TR]
</tbody>[/TABLE]
the current snippet display me the data i need in a pivot table BUT i don't know how to make the first column showing only data of last week and last column showing current week's data
any help VBA Gurus ?
i've managed to solve my biggest problem, showing a pivot table with sub-items
the data i have is data of last week and current week ( i have a date column in my array )
my pivot table right now is showing all the data, but instead i need the first column to count the data of last week ( based on date ) and the last pivot table column show the number of data of current week
[TABLE="width: 500"]
<tbody>[TR]
[TD]random data[/TD]
[TD]more random data[/TD]
[TD]stuff[/TD]
[TD]15/03/2015[/TD]
[/TR]
[TR]
[TD]random data[/TD]
[TD]random[/TD]
[TD]data[/TD]
[TD]10/03/2015[/TD]
[/TR]
[TR]
[TD]random[/TD]
[TD]data[/TD]
[TD]some[/TD]
[TD]23/03/2015[/TD]
[/TR]
[TR]
[TD]some[/TD]
[TD]random[/TD]
[TD]data[/TD]
[TD]23/03/2015[/TD]
[/TR]
[TR]
[TD]again[/TD]
[TD]some[/TD]
[TD]random[/TD]
[TD]18/03/2015[/TD]
[/TR]
[TR]
[TD]and[/TD]
[TD]some[/TD]
[TD]more[/TD]
[TD]19/03/2015[/TD]
[/TR]
</tbody>[/TABLE]
the current snippet display me the data i need in a pivot table BUT i don't know how to make the first column showing only data of last week and last column showing current week's data
Code:
Sub createPivotTable1()
'refer Image DataSource for source data. Note that the complete data table extends upto row number 49.
'refer Image 1 for PivotTable report created after running below code
Dim PvtTbl As PivotTable
Dim wsData As Worksheet
Dim rngData As Range
Dim PvtTblCache As PivotCache
Dim wsPvtTbl As Worksheet
Dim pvtFld As PivotField
'determine the worksheet which contains the source data
Set wsData = Worksheets("Feuil3")
'determine the worksheet where the new PivotTable will be created
Set wsPvtTbl = Worksheets("Report")
'delete all existing Pivot Tables in the worksheet
'in the TableRange1 property, page fields are excluded; to select the entire PivotTable report, including the page fields, use the TableRange2 property.
For Each PvtTbl In wsPvtTbl.PivotTables
If MsgBox("Supprimer le tableau croisé dynamique existant?!", vbYesNo) = vbYes Then
PvtTbl.TableRange2.Clear
End If
Next PvtTbl
'A Pivot Cache represents the memory cache for a PivotTable report. Each Pivot Table report has one cache only. Create a new PivotTable cache, and then create a new PivotTable report based on the cache.
'set source data range:
Set rngData = wsData.Range("A1:AG49")
'for creating a Pivot Cache (version excel 2007), use the PivotCaches.Create Method. When version is not specified, default version of the PivotTable will be xlPivotTableVersion12.
'The PivotCache.CreatePivotTable method creates a PivotTable report based on a Pivot Cache. TableDestination is mandatory to specify in the method.
'create Pivot Cache and PivotTable version excel 2007:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:=wsPvtTbl.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
Set PvtTbl = wsPvtTbl.PivotTables("PivotTable1")
'Default value of ManualUpdate property is False wherein a PivotTable report is recalculated automatically on each change. Turn off automatic updation of Pivot Table during the process of its creation to speed up code.
PvtTbl.ManualUpdate = True
'add row, column and page (report filter) fields:
Set pvtFld = PvtTbl.PivotFields("ACENERG201")
pvtFld.Orientation = xlPageField
Set pvtFld = PvtTbl.PivotFields("ACOBS001")
pvtFld.Orientation = xlRowField
Set pvtFld = PvtTbl.PivotFields("TELEFON")
pvtFld.Orientation = xlRowField
pvtFld.Position = 2
Set pvtFld = PvtTbl.PivotFields("ACOBS001")
pvtFld.Orientation = xlRowField
'set data field - specifically change orientation to a data field and set its function property:
With PvtTbl.PivotFields("TELEFON")
.Orientation = xlDataField
'.Function = xlSum
'.NumberFormat = "#,##0"
.Position = 1
End With
'turn on automatic update / calculation in the Pivot Table
PvtTbl.ManualUpdate = False
'Application.Version Property - returns the Excel version number in which VBA is being executed viz. 9.0, 10.0, 11.0, 12.0, etc.
MsgBox Application.Version
'PivotTable.Version Property - returns the PivotTable version number, 0, 1, 2, 3, etc.
MsgBox PvtTbl.Version
End Subany help VBA Gurus ?
