PDA

View Full Version : Solved: Macro to creat Pivot Table: help required



satish gubbi
12-06-2012, 02:32 AM
I have the below code to creat the pivot, I need to alter the below code so that it can be used any active worksheet with any independent data range.

I mean to say, I need to use this code on any active spreadsheet with any data range

source data has to be independent.


Sub Macro2()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'NHS'!R1C1:R93C13").CreatePivotTable TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("LOC").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2").PivotFields("CONTRACT").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("LOC", _
"CONTRACT"), ColumnFields:="PERIOD", PageFields:=Array("COMPANY", "PRIME")
ActiveSheet.PivotTables("PivotTable2").PivotFields("INVOICE VALUE"). _
Orientation = xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub


please help

p45cal
12-06-2012, 08:05 AM
this just might work:Sub Macro2()
Set pt = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="R1C1:R93C13").CreatePivotTable(TableDestination:=ActiveSheet.Cells(3, 1), DefaultVersion:=xlPivotTableVersion10)
With pt
.PivotFields("LOC").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("CONTRACT").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.AddFields RowFields:=Array("LOC", "CONTRACT"), ColumnFields:="PERIOD", PageFields:=Array("COMPANY", "PRIME")
.PivotFields("INVOICE VALUE").Orientation = xlDataField
End With
'ActiveWorkbook.ShowPivotTableFieldList = True' this line may not be necessary.
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

satish gubbi
12-06-2012, 09:49 AM
Thank you very much for your reply, however I want Pivot table in the seperate sheet, this code is replacing the content of the sheet.

kindly help so that Pivot can be created in seperate sheet

p45cal
12-06-2012, 01:14 PM
Regarding the Source data:
1. Is it on the active sheet?
2. Is there only one Source data on a particular sheet?
3. Are there several source data, one for eachPivot table?
4. anything else I should know?

Regarding the Pivot table location:
1. Is it on the active sheet?
2. Is it on a given sheet, always.
3. Is it to be put on a new sheet each time?
4. The Pivot table can be on a sheet (not necessarily the active sheet), to be determined somehow at the time the macro is run?
5. anythiung else I should know?

satish gubbi
12-06-2012, 09:14 PM
Regarding the Source data:
1. Is it on the active sheet? Yes, Data is on the active sheet
2. Is there only one Source data on a particular sheet? Yes, workbook will have only one sheet at the moment with data
3. Are there several source data, one for each Pivot table? No.
4. anything else I should know? Nothing specific

Regarding the Pivot table location:
1. Is it on the active sheet? Pivot table has to be on separate sheet
2. Is it on a given sheet, always. No, Pivot table has to be on new sheet
3. Is it to be put on a new sheet each time? YES
4. The Pivot table can be on a sheet (not necessarily the active sheet), to be determined somehow at the time the macro is run? Pivot table has to be on new sheet
5. anythiung else I should know? Nothing else

p45cal
12-07-2012, 05:56 AM
try:Sub blah()
Set SourceSht = ActiveSheet
Set NewSht = Sheets.Add
Set pt = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=SourceSht.Range("A1:M93")).CreatePivotTable(TableDestination:=NewSht.Range("A3"), _
DefaultVersion:=xlPivotTableVersion10)
With pt
.PivotFields("LOC").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("CONTRACT").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.AddFields RowFields:=Array("LOC", "CONTRACT"), ColumnFields:="PERIOD", PageFields:=Array("COMPANY", "PRIME")
.PivotFields("INVOICE VALUE").Orientation = xlDataField
End With
'ActiveWorkbook.ShowPivotTableFieldList = True' this line may not be necessary.
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
Currently the source range is hard coded (not the source sheet, that's the active sheet) to A1:M93 but this can be soft coded if necessary.

satish gubbi
12-10-2012, 02:41 AM
Thank you very much, this Code is working as intended, however can we have soft code for source data, (let the code decide the data range) and Data field "Invoice Value" showing the count instead of sum,

please help

p45cal
12-10-2012, 05:48 AM
you could try, but I don't know how the sheet is laid out, replacing:
SourceSht.Range("A1:M93")
with:
SourceSht.Range("A1").currentregion

Regarding Count versus Sum, try adding the line:
.PivotFields("INVOICE VALUE").Function = xlCount

but it may be wrong as I don't know whether the header "INVOICE VALUE" has been during pivot table crteation or is a pre-existing header. You may have to chenge "INVOICE VALUE" in the code to something else.

satish gubbi
12-10-2012, 10:04 AM
Hi,
thank you very much for your reply, I tried to execute the code, however I did not work as intended.

attached is the file for your reference. Kindly help me in this regard.

p45cal
12-10-2012, 11:11 AM
Sub blah()
Set SourceSht = ActiveSheet
Set NewSht = Sheets.Add
Set pt = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=SourceSht.Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=NewSht.Range("A3"), DefaultVersion:=xlPivotTableVersion10)
With pt
.PivotFields("LOC").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("CONTRACT").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.AddFields RowFields:=Array("LOC", "CONTRACT"), ColumnFields:="PERIOD", PageFields:=Array("COMPANY", "PRIME")
With .PivotFields("INVOICE VALUE")
.Orientation = xlDataField
.Function = xlCount
End With
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

satish gubbi
12-10-2012, 09:37 PM
Thank you very much P45cal, this code is working as intended.

Thank you very much once again.