Creating a table from multiple PivotTables usable for making a report sheet

[maxveller]

Hi Excel Forum, this is my first post.

I'm in deep need of help. I work as a student helper, and my job is to create reports for each of the departments of the school I work for. These reports include showing the changes in Headcounts from quarter to quarter and the distribution of the different professions for each department.

I have created a Pivot Table for each quarter I have data for. Now I need to find a way to "combine" these Pivots in such a way, that I will be able to show the changes that happen from quarter to quarter in each department. I want the row labels to include each sub-department (there can be more sub-departments in quarter 2 than quarter 1 and this has to update automatically) and I want the column labels to state each quarter with the corresponding data down through the columns.

I am currently working in Excel 2010 as this is the version used by my boss and coworkers.

I have been fiddling around with this for so long and I am yet to find a solution to my problem.

Every attempt of helping me will be much appreciated!

- Max

 

[maxveller]

Hey Julian!

I've uploaded a more accurate Dummy File:
http://www.filedropper.com/dummyfile2

This one has all the right sheets with all the right headers and column numbers.

I need to somehow be able to combine each sheet type into 4 master sheets with all the data. Making the Pivots afterwards will then be a cakewalk I believe

Max

 

[j_unsuitable]

Hey

check out if this works as you want it to:

http://www.filedropper.com/kopievondummyfile22

My danish is nonexistent so excuse me if oversigt and samlede are the wrong words haha

I also wasnt sure if you wanted four individual macros or one for all, so I created the individual ones plus one that just opens the four individual macros.
And finally I wasnt sure what the data in the Quarter column should look like. Right now its just the number. If you want it to be K1? Q1? Thats an easy fix.
Oh and remember: it deletes the "Samlede" Sheets each time the macro runs, so dont put your Pivottables in there.


Julian

 

[maxveller]

Hey Julian! It works fine! I just want to know how to change the data in the Kvartal column to Kvartal 1, Kvartal 2 ... Instead of 1,2,3,4

Max

 

[j_unsuitable]

Hey

Just change this line of code:

Quarter = Left(Quarter, 1)

to

Quarter = "Kvartal " & Left(Quarter, 1)

in the four Macros.

Julian