VBA code to build Pivot tables in “Existing sheets”.

[Hudson]

Hi all,


I was trying to do some pivot automatons in existing sheets(summary) . attached is the sample file for your reference . can you help with the VBA code .. its a piece i have given you as a sample there are more pivots needs to be added in the same summary sheet along with few more calculations... and narrations that i can do my self .. but i am unable to create code in existing sheet..any inputs are appreciated.

[p45cal]

cross posted:
http://www.excelforum.com/excel-prog...ng-sheets.html

Have a read of http://www.excelguru.ca/content.php?184

[p45cal]

some pivot automatons in existing sheets(summary)

Could you be more specific? What needs to be automated?

[Hudson]

Hi all.

Before I take any help or advice from this forum . I would like to inform you that I have cross posted the question at below forum.

http://www.vbaexpress.com/forum/showthread.php?57746.

and it does not mean to waste our valued experts or forum members . but I was expecting a quick resolution . assuming I would close the question which ever get resolved .


thanks ...

[Hudson]

Hi mate(P45cal).

thanks for your come back on my question . its simple that , I wanted to do a pivot table using a data in sheet1 in summary sheet.

macro is not working for this . hence code required for it , can you help me ?.

[p45cal]

Sub blah()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("A19"))
  .PivotFields("Credit analyst").Orientation = xlRowField
  .AddDataField .PivotFields("Total balance in LC"), "Sum of Total balance in LC", xlSum
  .AddDataField .PivotFields("Overdue Total"), "Sum of Overdue Total", xlSum
  .DisplayFieldCaptions = False
  .PivotFields("Sum of Total balance in LC").NumberFormat = "£#,##0.00"
  .PivotFields("Sum of Overdue Total").NumberFormat = "£#,##0.00"
  .TableStyle2 = "PivotStyleLight16"
  .ShowTableStyleRowStripes = True
End With
End Sub

ps. you've linkd to this thread rather than the other forum(s)

[Hudson]

thanks mate. I will check and let you know .. and thanks for your assistance. have a grate day

[Hudson]

Hi P45cal .

This is fantastic mate . can that be made more dynamic lets say . I have another data with attached file . this is something different date I have send you initially .

can you advice please ?.

[Hudson]

Just wanted to let you know .. I have asked this similar question in below forum . but not exactly

http://www.excelforum.com/showthread...t=#post4529219

[p45cal]

Sub Macro1()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Sheet1").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("A19"))
  With .PivotFields("Classification")
    .Orientation = xlRowField
    .Position = 1
  End With
  With .PivotFields("Ageing Buckets as on today")
    .Orientation = xlColumnField
    .Position = 1
  End With
  .PivotFields("Ageing Buckets as on today").PivotItems(">120").Position = 5
  .AddDataField .PivotFields("Amount in doc. curr."), "Sum of Amount in doc. curr.", xlSum
End With
End Sub

[Hudson]

Mate- this is excellent you are the best . I wish I could give you one rep but don't have one to give.

mate this is fine . how do I make sum to count .for the field : (Amount in doc. curr.)

Can you advice please

[p45cal]

change:
.AddDataField .PivotFields("Amount in doc. curr."), "Sum of Amount in doc. curr.", xlSum
to:
.AddDataField .PivotFields("Amount in doc. curr."), "Count of Amount in doc. curr.", xlCount