VBA to create dynamic table/pivot table to auto refresh charts on other sheets
I am an intern at a corporate office for a hospital and I am fixing their worker productivity dashboard. I am pretty good with Excel itself, but a novice at VBA. I need to be able to autofilter a table based on what a reference cell says (a hospital's abbreviation code), so that on another sheet I can format the graphs to reference a dynamic pivot table so that when I change the name of the hospital we are looking at, the data changes accordingly. The raw data is located on sheets that are separate from the display sheets because the executives only want to see the summary pages. Sheet 2 is various charts and tables that have been set up to change when I change the reference cell, which is D2 on Sheet 2, from "ABC" to "CDE" the data changes from ABC's data to CDE's data. Sheet 3 is raw data that I want to have filter automatically so that I can have Sheet 2's charts change automatically.
Here is what I need to do:
- Take data from data range located at A1:H189 on Sheet 3 (will grow vertically as Fiscal Year advances) and make it autofilter based on either a cell on another sheet, or a cell on that sheet that references the other sheet's cell (so that when I change the hospital we are looking at, the table filters to include only that hospital's data)
- Use this auto-refreshing dynamic table to create a pivot table that automatically refreshes based on the table, so that I can change the range that the line graphs and such on Sheet 2 reference
I do not want to have to manually refresh the data every time that something changes, I have used the VBA code
Application.EnableEvents = False
Application.EnableEvents = True
to automatically refresh a Pivot Table when the reference data range has cell changes, but I don't know how I would use this in the situation I am in now, if I could at all.
What I am trying to filter is Column A on Sheet 3, named "ENTITY ID", the other columns do not need to be filtered.
I should make it known that I already made these graphs dynamic before, by referencing tables that were dynamic using IF statements, but I want to make it so that if we acquire new hospitals the tables will automatically take this new organization into account rather than I need to manually add in this new organization into the IF statements.
If there is an easier way to do this than what I wrote out be my guest, I just don't know any other way to do it with the FILTER function being and beta and therefore not being available yet.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By excelsorusor in forum Excel General
Last Post: 11-12-2018, 02:46 PM
By bhenlee in forum Excel Programming / VBA / Macros
Last Post: 08-18-2015, 11:38 PM
By okl in forum Excel Programming / VBA / Macros
Last Post: 02-01-2010, 09:38 AM
By jimmisavage in forum Excel General
Last Post: 12-15-2006, 08:08 AM
By email@example.com in forum Excel General
Last Post: 04-18-2006, 05:35 PM
By Luc Poppe in forum Excel General
Last Post: 09-10-2005, 01:05 PM
By mdalby in forum Excel General
Last Post: 04-11-2005, 08:14 PM
Tags for this Thread
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1