pivot table from multiple dynamic tables

hi guys have got 7 sheets in a workbook all of which have the same dynamic table layout (the tabs seperate the sites that need to fill this in)

i would like to create one pivot table to have a quick referance to all the sites (as the header fields are same)

the sites are

EMA
PCF
ANSTY
BTFF
BTFM
ROSS
HPMC

each table has its name as the site ( in name manager).
When chooseing the range for my pivot table i played ignorant and typed in "EMA,PCF,ANSTY......." but came up with a data range error so i tryed "EMA[#All],PCF[#All],......." still same error is there a way that i can make one dynamic table from the 7 sepperate tables then pivot or a way to pivot all 7? i only need 5 fields in my pivot " part, Status, Comments, Site and date"

any help would be brilliant

Many thanks