Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-1488909%22%20slang%3D%22en-US%22%3EPivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1488909%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20make%20an%20Excel%20spreadsheet%20and%20load%20the%20pivot%20tables%20you%20want%20to%20use%20to%20each%20tab%20and%20then%20copy%2Fpast%20the%20raw%20data%20on%20another%20tab%20to%20have%20it%20auto%20populate%20into%20the%20tables%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20wondering%20if%20I%20need%20to%20be%20manually%20copying%2Fpasting%20and%20inserting%20pivot%20tables%20to%20the%20same%20report%20each%20day%20or%20if%20there%20is%20a%20%22smarter%22%20way%20of%20doing%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1488909%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489305%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489305%22%20slang%3D%22en-US%22%3EYes%20that%20is%20exactly%20what%20you%20should%20be%20doing%3A%20Have%20a%20separate%20worksheet%2C%20perhaps%20called%20Data%2C%20on%20which%20you%20keep%20your%20data.%20How%20that%20data%20gets%20on%20that%20sheet%20depends%20on%20where%20the%20data%20currently%20is.%20You%20state%20paste%2C%20where%20did%20you%20copy%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489308%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710145%22%20target%3D%22_blank%22%3E%40Tracey_2020%3C%2FA%3E%26nbsp%3BThere%20is%20a%20smarter%20way%20for%20it%20using%20a%20VBA%20and%20creating%20a%20button%20for%20it%20I%20can%20do%20it%20for%20you.%20If%20you%20give%20a%20excel%20file%20with%20an%20example%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489356%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489356%22%20slang%3D%22en-US%22%3E%3CP%3EI%20copied%20the%20data%20from%20a%20report%20that%20is%20generated%20automatically%20and%20sent%20over%20each%20morning.%20I%20use%20the%20pivot%20table%20to%20organize%20the%20data%20into%20sections%20to%20review%20inventory%20data.%3C%2FP%3E%3CP%3EI%20generated%20my%20report%20using%20the%20data%20that%20is%20auto%20generated%20and%20inserted%20pivot%20tables%20to%20different%20tabs%20to%20organize%20the%20information%20%2F%20break%20down%20the%20data%20sent.%20I%20tried%20to%20copy%20and%20paste%20the%20raw%20data%20from%20a%20new%20report%20into%20a%20previous%20report%20to%20see%20if%20the%20information%20filtered%20by%20the%20pivot%20tables%20would%20update%20too%20but%20it%20didn't.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489458%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F710145%22%20target%3D%22_blank%22%3E%40Tracey_2020%3C%2FA%3E%26nbsp%3BThat%20report%20you%20are%20talking%20about%2C%20is%20that%20by%20any%20chance%20a%20CSV%20file%3F%20You%20can%20import%20CSV%20files%20into%20your%20source%20data%20worksheet%20directly%20by%20using%20Data%2C%20Get%20Data%2C%20From%20File%2C%20From%20CSV.%20If%20the%20name%20of%20the%20file%20differs%20each%20time%2C%20you%20can%20also%20use%20the%20%22From%20Folder%22%20feature%20and%20then%20filter%20the%20list%20of%20files%20by%20their%20date%20so%20the%20most%20recent%20is%20on%20top.%20Then%20delete%20all%20rows%20except%20the%20first%20row%20and%20finally%20expand%20the%20data.%20Now%20once%20the%20data%20is%20in%20a%20table%20on%20a%20worksheet%2C%20you%20can%20point%20your%20existing%20pivottables%20at%20the%20new%20data.%20WHen%20all%20of%20that%20is%20done%2C%20you%20simply%20open%20your%20file%20and%20click%20Data%2C%20Refresh%20All.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there a way to make an Excel spreadsheet and load the pivot tables you want to use to each tab and then copy/past the raw data on another tab to have it auto populate into the tables?

 

Just wondering if I need to be manually copying/pasting and inserting pivot tables to the same report each day or if there is a "smarter" way of doing it. 

 

Thanks!

4 Replies
Yes that is exactly what you should be doing: Have a separate worksheet, perhaps called Data, on which you keep your data. How that data gets on that sheet depends on where the data currently is. You state paste, where did you copy?

@Tracey_2020 There is a smarter way for it using a VBA and creating a button for it I can do it for you. If you give a excel file with an example

I copied the data from a report that is generated automatically and sent over each morning. I use the pivot table to organize the data into sections to review inventory data.

I generated my report using the data that is auto generated and inserted pivot tables to different tabs to organize the information / break down the data sent. I tried to copy and paste the raw data from a new report into a previous report to see if the information filtered by the pivot tables would update too but it didn't. @Jan Karel Pieterse 

@Tracey_2020 That report you are talking about, is that by any chance a CSV file? You can import CSV files into your source data worksheet directly by using Data, Get Data, From File, From CSV. If the name of the file differs each time, you can also use the "From Folder" feature and then filter the list of files by their date so the most recent is on top. Then delete all rows except the first row and finally expand the data. Now once the data is in a table on a worksheet, you can point your existing pivottables at the new data. WHen all of that is done, you simply open your file and click Data, Refresh All.