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?
p45cal Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
try:[vba]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
[/vba]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.
p45cal Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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,
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.
p45cal Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Reply With Quote
