excel vba - Merge multiple PivotTables for multiple workbooks to create a master PivotTable -

i've found code i've (mostly) modified use, getting error on grouping function. have folder has (at present) 3 workbooks in them. each workbook formatted same sheet names fields within each sheet. each workbook has 2 pivottables derived same unique data source (a third sheet in workbook).

i need able to, in new workbook, run script allow me choose workbooks common folder want combine 1 master pivot table. source data looks this:

(slashes used after names each column , after data in row 2 there differentiate different columns (12 in total, l inclusive))

row 1 - line / sort / sub-cat / part / para / page / deliv / action / owner / duedate / status / datecomp

row 2 - 2 / b / confrnc / 2 / 2.2.1 / 8 / attend / attend / john / 23-may-13 / notstarted / (blank)

each workbook has data source sheet set this, multiple rows of data.

each workbook has pivot table compiles:

rows:

1. sub-cat;
2. action;
3. owner;
4. status

columns:

1. duedate

values:

1. count of action

i have following piece of code have modified meet needs copied , pasted new module in new workbook (saved in same folder source workbooks):

---

option explicit   declare function setcurrentdirectorya lib "kernel32" (byval path string) long  '--------------------------------------------------------------------------------------- ' author: rob bovey '--------------------------------------------------------------------------------------- sub chdirnet(path string)     dim result long     result = setcurrentdirectorya(path)     if result = 0 err.raise vbobjecterror + 1, "error changing new path." end sub  '--------------------------------------------------------------------------------------- ' procedure : mergefiles ' author    : kl ' date      : 22/08/2010 ' purpose   : demonstration (http://www.planetaexcel.ru/forum.php?thread_id=18518) ' comments  : special '             debra dalgleish helping fix odbc driver issue '             hector miguel orozco diaz "deleteconnections_12" idea '--------------------------------------------------------------------------------------- ' sub mergefiles()     dim pt pivottable     dim pc pivotcache     dim arrfiles variant     dim strsheet string     dim strpath string     dim strsql string     dim strcon string     dim rng range     dim long      strpath = curdir     chdirnet thisworkbook.path      arrfiles = application.getopenfilename("excel workbooks (*.xlsx), *.xlsx", , , , true)     strsheet = "deliverables"      if not isarray(arrfiles) exit sub      application.screenupdating = false      if val(application.version) > 11 deleteconnections_12      set rng = thisworkbook.sheets(1).cells     rng.clear     = 1 ubound(arrfiles)         if strsql = ""             strsql = "select * [" & strsheet & "$]"         else             strsql = strsql & " union select * `" & arrfiles(i) & "`.[" & strsheet & "$]"         end if     next     strcon = _         "odbc;" & _         "dsn=excel files;" & _         "dbq=" & arrfiles(1) & ";" & _         "defaultdir=" & "" & ";" & _         "driverid=790;" & _         "maxbuffersize=2048;" & _         "pagetimeout=5"      set pc = thisworkbook.pivotcaches.add(sourcetype:=xlexternal)      pc         .connection = strcon         .commandtype = xlcmdsql         .commandtext = strsql         set pt = .createpivottable(tabledestination:=rng(6, 1))     end      pt         .pivotfields(1)                             'sub category             .orientation = xlrowfield             .position = 1         end         .adddatafield .pivotfields(8), "duedate", xlcount 'action required         .pivotfields(1)                             'action required             .orientation = xlrowfield             .position = 1         end         .pivotfields(1)                             'owner             .orientation = xlrowfield             .position = 1         end         .pivotfields(2)                             'status             .orientation = xlrowfield             .position = 1         .datarange.cells(1).group _                 start:=true, _                 end:=true, _                 periods:=array(false, false, false, false, true, false, false)         end     end      'clean     set pt = nothing     set pc = nothing      chdirnet strpath     application.screenupdating = true end sub  private sub deleteconnections_12()     '   line won't work , wouldn't necessary     '   in versions older 2007     '*****************************************************************************     on error resume next: thisworkbook.connections(1).delete: on error goto 0     '***************************************************************************** end sub   private sub worksheet_selectionchange(byval target range)  end sub 

---

when run script, line 92, run-time error 1004: cannot group selection.

.datarange.cells(1).group _                 start:=true, _                 end:=true, _                 periods:=array(false, false, false, false, true, false, false) 

---

for life of me, i'm lost , cannot find anywhere fix this.

can make recommendations or suggestions?

i still new @ vba, not pivottables. trying avoid having manually compile of data source workbooks master , running pivottable there, because workbooks owned 3 different users , updated regularly. utilizing offset formula name source data range, , using data source pivottables update @ once, , formula automatically increases range include new rows or columns have been added source data sheet.

i recognize because works grouping point, doesn't mean variables pivotfields done correctly either - if sees there - i'm open hearing it!

i working in excel 2013 , 2010.

transfer question appears answer, or near achieved:

here screen shots of both data set of pivot tables derived each individual workbook's data set, , how want running script:

looking @ @kazjaw comments, i've researched range.group , looked @ periods portion. ended deleting , ran script without problem! have manually adjust field lists , formatting, that's easy part compared pulling actual data , ever changing.