Auto Create graphs

[RodneyC]

I have a ton of data that gets updated every month, multiple spreadsheets, each with sometimes as many as 20,000 rows. I condense this down to a few Key Performance Indicators (KPI's) and convert it to graphs. The data for the graphs is always located on the same cell range every month and the type of graph wanted will always be the same. It takes quite a while to recreate these graphs every month and wish there was a way to automate the process via Visual Basic, Macros or something else, but I've never stepped into either of those worlds.

Creating the graphs would be step 1. Once I learn how to do that, step 2 would be to autogenerate or generate with a click of a button, a PDF of the graph.

Any suggestions, advice or direction to point me?

Thanks

 

[rlv01]

As I see it there are four main avenues.

1. Experiment with the VBA macro recorder. For the most part, anything you can do manually to your spreadsheet (creating graphs, etc) can be recorded by the macro recorder and played back to automate the actions.

2. Hope that someone here comes along who has the required VBA skill set and is interested enough to take this on.

3. Roll up your sleeves and dive into the world of VBA programming. The learning curve might be a bit steep at first, but it pays a lot of dividends later on if you work with Excel a lot. I don't know if you have noticed, but for the most part people who attempt to code their own stuff get fairly rapid responses to their posts for help here. That's because the coders here remember their own struggles and want to help - while people who post project specifications ("I want someone to make me a macro that does x, y, and z") depend on the random chance of catching someone's interest.

4. Hire someone to do it for you.

Since you say that the data is always in the same range, and the charts are the same then for you I'd recommend exploring #1 (macro recorder). Spend a couple of hours experimenting and see if it will do what you want. If you get something that comes close, you can post the code here for advice on how to tweak it.

Update: One thing I should have mentioned is Power Query or Power BI. I don't use these features myself so I don't know a lot about them, but it's possible they could help. But you would need to ask that question in the Power BI forum here.

 

[Logit]

Excel has "CHARTS" .... is it charts that you are referring to when you say GRAPH ? If not .... please provide a sample of a GRAPH.

To assist you ... please provide a sample of your workbook that can be downloaded from a 'cloud' website. A suggestion would be DROPBOX.COM

 

[RodneyC]

Excel has "CHARTS" .... is it charts that you are referring to when you say GRAPH ? If not .... please provide a sample of a GRAPH.

To assist you ... please provide a sample of your workbook that can be downloaded from a 'cloud' website. A suggestion would be DROPBOX.COM

You are right, it's a "Chart" not a graph. Here is a Drop Box link: DEMO.xlsx

Look on rows 3 through 13, columns S through AJ. Each of the colored areas are what I'm trying to create charts for. I've manually done two of them for demo purposes, you can see these in rows 15 through 35. Every month the data in columns A through I will change. The formulas in S3:S12 through AJ3:AJ12 will read the data and populate the fields appropriately. I'd like for the graphs to be autogenerated then even better, auto-create a PDF if possible. Copy/pasting the charts into a Power Point could easily be done so this PDF part would be icing on the cake.

THANK YOU so much for looking at this.

 

[Logit]

Regretfully, I am unable to create the charts you are needing. Perhaps another member of the Forum can assist.

Best wishes on your project.

 

[RodneyC]

Regretfully, I am unable to create the charts you are needing. Perhaps another member of the Forum can assist.

Best wishes on your project.

Thank you for your efforts. Have a Happy Thanksgiving

 

[Logit]

Happy Thanksgiving