pivot table placed in multiple tabs within a workbook?

[Nick123456]

Hi Excel Gurus! I’m new here and have a Pivot Table question. On a weekly basis I create a report for 9 retail locations. Each of these locations has their own tab in the workbook with information unique to that location. The data is formatted the same meaning columns A – F in each tab contain: SKU/Description/Retail Amount/QTY Sold/Volume. This raw data represents each item sold for each location and I use a pivot table to total the QTY Sold, find the Max value for the Retail Amount and total the Volume by SKU. This allows me to track SKU trends by store and as a district on the whole. The information is always formatted the same by column but contains a different number of rows based on how many SKU each location has sold. My question is how do I get the pivot table into each tab without manually clicking on each tab and recreating it? At this point I’m creating 9 pivot tables every time I run this report and the company is expanding. I’m not great at creating Macros so if this is the direction I need to take I’ll need some details. I’ve been able to create a Macro that works in one tab but it never transfers to a different tab. Sorry for the long post! Thanks!
TL;DR: How do I insert a pivot table into multiple tabs within a workbook?

I've added an example attachment that may make things clearer. I need to recreate the pivot table from the first tab on every tab. I hope there's a way to do this without manually using the pivot function over and over. Thanks!

[Dimitrov]

You may not need to recreate the pivot tables each time. If you can use the same spreadsheet over and over again and are willing to clear the data for each location, you can simply refresh the pivot table. For example, consider your Location 1 tab. If you clear out the data (leave the column headings intact), then paste in new data, you simply refresh the pivot table. The refreshed pivot table should pick up the new set of data. This should be an improvement over what you are doing now (i.e., building a new pivot table for each location each month). Refreshing a pivot table does not require a macro. Just click anywhere in your pivot table, and you will be taken to your pivot table tools (in your ribbon). Under the ANALYZE menu, there should be a refresh option. Click that.


However, you will eventually get tired of manually refreshing each pivot table. What you can then do is to write a macro that automatically goes into each tab and refreshes that tab's pivot table (after you put in new data each month). You can do this with loops in VBA. At this point, you will need to implement this with a macro. Here is VBA code that should help:

Sub RefreshMyPivots()

Dim w As Worksheet
Dim p As PivotTable

For Each w In Worksheets
    For Each p In w.PivotTables
        p.RefreshTable
        p.Update
    Next p
Next w

End Sub

Hope this points you in the right direction.

_________________________________
1. If this reply was helpful, click the star to the left.
2. If this reply answered your question, mark this thread as [SOLVED].

[Nick123456]

Thanks for the response! I'll have to get better with Macros.

[Dimitrov]

Nick123456,

First, there are many resources that you can tap into to learn about macros and VBA programming. When I got started, I used some online tutorials and books to help me learn. I hope you will have a fun journey.

Second, if you find that my replies were helpful, click the star to the left, and if you feel this thread has satisfactorily answered your inquiry, please kindly mark it as [SOLVED].

Thank you.