Thank you I got the solution. I need to know why Adddatafield is used?
On Fri, Jul 8, 2011 at 9:32 AM, Chandra Shekar <chandrashekarb....@gmail.com
> wrote:
> 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
