PDA

View Full Version : VBA code to build Pivot tables in “Existing sheets”.



Hudson
11-16-2016, 11:59 PM
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
11-17-2016, 05:45 AM
cross posted:
http://www.excelforum.com/excel-programming-vba-macros/1163604-how-to-create-macro-for-pivot-tables-in-existing-sheets.html

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

p45cal
11-17-2016, 06:00 AM
some pivot automatons in existing sheets(summary)Could you be more specific? What needs to be automated?

Hudson
11-17-2016, 06:43 AM
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
11-17-2016, 06:46 AM
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
11-17-2016, 06:50 AM
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
11-17-2016, 07:00 AM
thanks mate. I will check and let you know .. and thanks for your assistance. have a grate day :)

Hudson
11-24-2016, 09:05 AM
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
11-24-2016, 09:20 AM
Just wanted to let you know .. I have asked this similar question in below forum . but not exactly

http://www.excelforum.com/showthread.php?t=1164545&p=4529219&highlight=#post4529219

p45cal
11-24-2016, 09:46 AM
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
11-24-2016, 11:17 AM
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
11-24-2016, 04:36 PM
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