Good Afternoon,
I am attempting to create a dashboard that will allow me to simply extract two individual reports from my companies content management system, copy and paste their data sets into tables within a workbook, refresh all data, and populate a nice dashboard with different charts based on pivot tables. I have a general understanding of how to do this manually, but refining large data sets over and over gets old. Unfortunately I cannot share data so I will do my best to explain my current steps.
First off, to make things easy we manufacture a product for sales. Products unique names, and unique codes.
The reports I extract from our content management system is a review of production, quality control, etc.... I can export an individual day or any range of dates/years as preferred. Sometimes ill grab a full year.
Report shows me;
Production Date
Product Code
Product Name
QC's results etc
I'm using a query from the "data input" tab to transform and add some columns that did not exist in the original data set to a new sheet named "Summary". I format the "refined" data as a table in the sheet called Summary, which then I turn into pivot tables and charts on the dashboard tab.
Where I am really struggling is;
1.) I added macros to clear the "input" tables and delete all rows except headers, and one additional blank row that I can copy my new data into.
2.) I recorded a macro that was essentially just me clicking refresh all (data) and added it to a button like the clear macros.
Now, when I clear the input table/query table and extract a new date range of data from our CMS, copy and paste it into the "data input" tab/table, go to the "Summary" tab, click the refresh macro, it pulls the former date ranges and not the new ones that i just copied and pasted into input table.
TLDR:
I want to create a report with an "Input" tab that is just a copy and paste of "fresh" data. Clean/add/refine through query using refresh macro to summary tab, formats as a table, which then i can push pivot tables through.
Any ideas would be greatly appreciated. I have gotten very close, but kind of going into this blind. The one I though I had just the way i wanted it the data connection broke the next morning when i opened the file.
Bookmarks