MACRO that creates Pivot Table on several worksheets (tabs) - Microsoft Community
hello,
the excel 2016 workbook i’m working on has +100 worksheets (tabs), each worksheet (tab) has 4 columns of data have same heading entire workbook. performing recurring analysis require extracting , generating new forms of data utilizing “pivot table” capabilities. obviously, time consuming individually, instead thought of recording macro (ctrl + g) but didn’t work other worksheets because refers original source of data , destination according vba code lines 7, 8, 9, , 10 in bold font. programming skills weak , seeking help.
error message: microsoft visual basic window / run-time error ‘5’: invalid procedure call or argument
sub max_processing_time()
'
' max_processing_time macro
'
' keyboard shortcut: ctrl+g
'
activeworkbook.pivotcaches.create(sourcetype:=xldatabase, sourcedata:= _
"feb, 2008!r6c35:r195c38", version:=xlpivottableversion10).createpivottable _
tabledestination:="feb, 2008!r7c39", tablename:="pivottable1", _
defaultversion:=xlpivottableversion10
sheets("feb, 2008").select
cells(7, 39).select
with activesheet.pivottables("pivottable1")
.columngrand = true
.hasautoformat = true
.displayerrorstring = false
.displaynullstring = true
.enabledrilldown = true
.errorstring = ""
.mergelabels = false
.nullstring = ""
.pagefieldorder = 2
.pagefieldwrapcount = 0
.preserveformatting = true
.rowgrand = true
.savedata = true
.printtitles = false
.repeatitemsoneachprintedpage = true
.totalsannotation = false
.compactrowindent = 1
.ingriddropzones = true
.displayfieldcaptions = true
.displaymemberpropertytooltips = false
.displaycontexttooltips = true
.showdrillindicators = true
.printdrillindicators = false
.allowmultiplefilters = true
.sortusingcustomlists = true
.fieldlistsortascending = false
.showvaluesrow = true
.calculatedmembersinfilters = false
.rowaxislayout xltabularrow
end with
with activesheet.pivottables("pivottable1").pivotcache
.refreshonfileopen = false
.missingitemslimit = xlmissingitemsdefault
end with
activesheet.pivottables("pivottable1").repeatalllabels xlrepeatlabels
with activesheet.pivottables("pivottable1").pivotfields("part no.")
.orientation = xlrowfield
.position = 1
end with
activesheet.pivottables("pivottable1").adddatafield activesheet.pivottables( _
"pivottable1").pivotfields("processing time"), "count of processing time", _
xlcount
activesheet.pivottables("pivottable1").adddatafield activesheet.pivottables( _
"pivottable1").pivotfields("processing time"), "count of processing time2", _
xlcount
with activesheet.pivottables("pivottable1").datapivotfield
.orientation = xlcolumnfield
.position = 1
end with
with activesheet.pivottables("pivottable1").pivotfields( _
"count of processing time2")
.caption = "max of processing time2"
.function = xlmax
.numberformat = "h:mm:ss;@"
end with
activesheet.pivottables("pivottable1").adddatafield activesheet.pivottables( _
"pivottable1").pivotfields("processing time"), "count of processing time2", _
xlcount
with activesheet.pivottables("pivottable1").pivotfields( _
"count of processing time2")
.caption = "average of processing time2"
.function = xlaverage
.numberformat = "h:mm:ss;@"
end with
activesheet.pivottables("pivottable1").adddatafield activesheet.pivottables( _
"pivottable1").pivotfields("processing time"), "count of processing time2", _
xlcount
with activesheet.pivottables("pivottable1").pivotfields( _
"count of processing time2")
.caption = "stddevp of processing time2"
.function = xlstdevp
.numberformat = "h:mm:ss;@"
end with
activesheet.pivottables("pivottable1").pivotfields("part no.").autosort _
xldescending, "max of processing time2"
activeworkbook.showpivottablefieldlist = false
end sub
thanks,
anas
ps no single change has been made of worksheets, columns intact, destination cell blank.
Office / Excel / Microsoft Office Programming
Comments
Post a Comment