change multiple pivots to new source data or tab

%3CLINGO-SUB%20id%3D%22lingo-sub-1674902%22%20slang%3D%22en-US%22%3Echange%20multiple%20pivots%20to%20new%20source%20data%20or%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1674902%22%20slang%3D%22en-US%22%3E%3CP%3Efor%20example%2C%20i%20have%2011%20pivots%20across%20multiple%20worksheets%20in%20one%20Excel%20workbook%2C%20i%20want%20to%20change%20at%20once%20all%20pivot%20tables%20to%20read%20e.g.%20SeptemberData%20(its%20own%20tab%2Fworksheet)%26nbsp%3B%20when%20it%20was%20originally%20AugustData%20(its%20own%20tab%2Fworksheet%3F%26nbsp%3B%20how%20do%20i%20do%20that%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1674902%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1675143%22%20slang%3D%22en-US%22%3ERe%3A%20change%20multiple%20pivots%20to%20new%20source%20data%20or%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1675143%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F270177%22%20target%3D%22_blank%22%3E%40Nor_D%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20I'd%20do%20it%2C%20but%20that%20probably%20won't%20work%20given%20how%20you've%20described%20your%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20have%20all%20data%20on%20one%20spreadsheet%2C%20with%20a%20column%20that%20designated%20which%20month%20the%20data%20referred%20to.%20Then%20a%20simple%20%22Filter%22%20on%20the%20Pivot%20Table(s)%20could%20be%20used%20to%20designate%20which%20month%20you%20wanted%20to%20display.%20You'd%20add%20data%20as%20you%20go%20along%2C%20noting%20the%20month%20applicable%2C%20then%20in%20the%20PivotTable%20region%20click%20on%20%22Refresh%20All%22%20after%20which%20the%20newest%20month%20(along%20with%20all%20previous%20ones)%20would%20be%20selectable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20really%20is%20good%20at%20selecting%20and%20retrieving%20data%20from%20a%20single%20database%20using%20selection%20criteria%20you%20the%20user%20provide.%20No%20need%20to%20do%20the%20separation%20yourself%20by%20such%20means%20as%20separate%20tabs%20for%20each%20month.%20Create%20a%20consolidated%20database%20of%20all%20data%3A%20you'll%20be%20amazed%20at%20how%20much%20more%20powerful%20and%20helpful%20reports%20you%20can%20create%2C%20comparing%20months%20(for%20example)%2C%20showing%20trends%20by%20month....%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Microsoft

for example, i have 11 pivots across multiple worksheets in one Excel workbook, i want to change at once all pivot tables to read e.g. SeptemberData (its own tab/worksheet)  when it was originally AugustData (its own tab/worksheet?  how do i do that? 

1 Reply
Highlighted

@Nor_D 

 

I know how I'd do it, but that probably won't work given how you've described your workbook.

 

I would have all data on one spreadsheet, with a column that designated which month the data referred to. Then a simple "Filter" on the Pivot Table(s) could be used to designate which month you wanted to display. You'd add data as you go along, noting the month applicable, then in the PivotTable region click on "Refresh All" after which the newest month (along with all previous ones) would be selectable.

 

Excel really is good at selecting and retrieving data from a single database using selection criteria you the user provide. No need to do the separation yourself by such means as separate tabs for each month. Create a consolidated database of all data: you'll be amazed at how much more powerful and helpful reports you can create, comparing months (for example), showing trends by month....