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

maxveller

New Member
Joined
Nov 9, 2015
Messages
7
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
 
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hey

Just change this line of code:
Code:
Quarter = Left(Quarter, 1)
to
Code:
Quarter = "Kvartal " & Left(Quarter, 1)
in the four Macros.

Julian
 
Upvote 0

Forum statistics

Threads
1,226,878
Messages
6,193,465
Members
453,802
Latest member
SarahNoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top