Pivot table from multiple datasets with transpose required (Excel 2010)

Question

I've read all the questions on pivots combining multiple datasets and have played around too and can't crack this one.

The data setup I have is (image included below):

• Multiple worksheets each with an identically set up Excel Table.
• The data tables have five categorical columns (B to F) and then 100 or so data columns (G to around DD).
• The categorical columns are all populated (and I need three of these in the final output); the data columns are mostly blank - only relevant cells to that tab are populated (only require non-zero amounts in the final output).

NOTE: This is output data from a system I cannot change and in a format I cannot change in the first instance. There is a clunky VBA solution that copies and pastes the data into a pivot-friendly format, from which a pivot table is generated. My objective is to generate the pivot table directly, removing two intermediate worksheets and the necessity to use code.

An abbreviated data format (3 categorical columns, 4 data columns):

|Variable Name|BS component|Source|Code1|Code2|Code3|Code4|

|VariableName1|BSComponnt1|SrceM|10000|20000|30000|40000|
|VariableName2|BSComponnt1|SrceM|99999|20000|44444|40000|
|VariableName2|BSComponnt2|SrceM|10000|22222|30000|40000|
|VariableName3|BSComponnt2|SrceM|10000|20000|00000|40000|

And now imagine a second identical table but with Source entries all 'SrceN'. There will be multiple tables like this that I need to display in a single pivot table.

Desired pivot table output is then:

• Rows that have Code as first level, which breaks down into Source

• Columns that go BS component, broken down into Variable name

  |Codes|Source|BS component1|_______________|BS component2|____________
  _______________Variable1_________Variable2____Variable1_________Variable2
  Code1  SrceM____SumData________SumData____SumData_________SumData
  ______SrceN____SumData________SumData____SumData_________SumData
  ______SrceP____SumData________SumData____SumData_________SumData
  Code2  SrceM____SumData________SumData____SumData_________SumData
  ______SrceN____SumData________SumData____SumData_________SumData
  ______SrceP____SumData________SumData____SumData_________SumData
  

Any thoughts on this? So data requires transposing of Codes from across the columns to down the rows as well as providing this breakdown. I've tried playing with field names etc and just cannot get it all to work!

Any help greatly appreciated please! :-)