Hi,
Sorry I could not able to provide data bcoz its highly confidential I ran
same using run macro I think lines which I have bolded has problem and I
don't know how to use Adddatafield in code my code.
Points what I am following is.
1) I am putting 3 fields i.e. Location, Asset and Report date in Row fields.
2) I am putting 2 fields i.e. BTN and Report date in Column field
3) while getting count I am facing problem I am getting all -4142.
4) But I am not getting Column Labels values when I ran the macro which I
have done. i thin problem lies in Adddatafield
Please let me know how to do this.
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"FormatData!R1C1:R238C13",
Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet3!R3C1", TableName:="PivotTable2",
DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet3").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Person
Location")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Asset")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Reported
DateTime")
.Orientation = xlRowField
.Position = 3
End With
* ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Bridge Ticket Number"), _
"Count of Bridge Ticket Number", xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Reported DateTime"), "Count of Reported
DateTime", _
xlCount
With ActiveSheet.PivotTables("PivotTable2").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
*End Sub
On Thu, Jul 7, 2011 at 8:46 PM, Rajan_Verma <rajanverma1...@gmail.com>wrote:
> *Please attached corresponding data*
>
> * *
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Chandra Shekar
> *Sent:* Thursday, July 07, 2011 8:15 PM
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Pivot Issue****
>
> ** **
>
> Hi,****
>
> ****
>
> The data is not displaying in the below code which has written for pivot.
> Please let me know where is the error is.****
>
> ****
>
> Thanks in advance****
>
> ****
>
> Sub pivot_table()****
>
> Dim pvt_ch As PivotCache
> Dim pvt_tbl As PivotTable
> Dim rng As Range****
>
> Set rng = ThisWorkbook.Worksheets("Formatdata").UsedRange
> Set pvt_ch = ThisWorkbook.PivotCaches.Add(xlDatabase, rng)****
>
> ThisWorkbook.Worksheets("Pivot").Select****
>
> Set pvt_tbl = pvt_ch.CreatePivotTable(Worksheets("Pivot").Range("B3"))
> With pvt_tbl
> .AddFields Array("Person Location", "Asset", "Reported DateTime")
> End With****
>
>
> With pvt_tbl
> .CalculatedFields.Add "Count of Bridge Ticket Number", xlCount
> .CalculatedFields.Add "Count of Reported DateTime", xlCount
> End With****
>
>
> With pvt_tbl.PivotFields("Count of Bridge Ticket Number")
> .Orientation = xlDataField
> .Function = xlCount
> End With****
>
> With pvt_tbl.PivotFields("Count of Reported DateTime")
> .Orientation = xlDataField
> ' .Function = xlCount
> End With****
>
>
> With pvt_tbl.DataPivotField
> .Orientation = xlColumnField
> .Position = 1
> End With****
>
> With pvt_tbl
> pitm_cnt = pvt_tbl.PivotFields("Person Location").PivotItems.Count
> For j = 1 To pitm_cnt
> pvt_tbl.PivotFields("Person Location").PivotItems(j).ShowDetail =
> False
> Next
> End With
> Application.CutCopyMode = False****
>
> End Sub****
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel****
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
