Create Pivot Table from multiple sheets

[ooitzechyi]

Hi, I'm have my macro to run in the way that user will define the no of sheets and the sheet.name to be create by input the name into textbox(1 to 12) on an userform.
Example:
TextBox1 = A
TextBox2 = B
TextBox3 = C
TextBox4 = D

Total 4 sheets will be created with name "A", "B", "C", "D".

However, I'm stuck at the last step to create a Pivot table which combine all info from those multiple sheets created by user.
Let's say in every sheet I have Column "Time" and Column "Activity".
And I would like to have PivotTable as below.
Activity 1 Activity 2 Activity 3
A Time 1A Time 2A Time 3A
B Time 1B Time 2B Time 3B
C Time 1C Time 2C Time 3C
D Time 1D Time 2D Time 3D

Please help~

[werafa]

try defining your data ranges as Dynamic Named Ranges, you can then call them programatically and pass them to your pivot table.
I have done a lot with pivots and VBA - but not with power pivots, so can't say for sure how you might do this.

I do also recommend that you check out Microsoft PowerBI. It will link to an excel table, and allow the generation of multi-table reports with ease