Creating Charts With VBA ..
May 26, 2009
I've got a table which has additives in Column A and products on Row 1. There are 13 additives and 10 products. Each combination of product and additive has a price. So I have 130 different prices in my table. The prices change monthly and the new prices are added to the sheet so that last months prices will move to left so that the newest price data table is A1:J13 and previous month is K1:T13 and so on.
I need to make an individual chart to every combination which contains the history of the price changes. I have two cells (A20 and B20) where the user can pick a product and an additive (created a menu by using Data|Validation...). These selections are combined in a cell C20 (=A20&B20). Then I have a macro that is linked to the cell C20 and is basically Case Is = 1st combination --> Call Macro1, Case Is = 2nd combination --> Call Macro2 etc.
That macro will work out just fine, BUT is there any way I could make all the 130 charts easier? Now I've recorded a macro that creates the first chart and then I've just copy-pasted it, changed the attributes (cell numbers, names and so on) that define the data to the next charts. This will take ages...
Every combination will have at least 13 changes (12 monthly price cells and the name of the cart) so in total this will mean about 1500 manual changes. In my case this will mean a high danger of typos.
I know, it would be SO much easier if I could give you the Excel file to look at...hopefully you understood what I mean.
I'll only follow the changes within a year so prices older than a year will not be shown in the charts.
View 9 Replies
ADVERTISEMENT
Jul 14, 2009
Now I need to create multiple pie charts based on data in tables on many sheets.
I need to create the same set of charts, for each sheet separately.
I recorded a macro for one sheet, which created following
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$B$2"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$D$6:$D$8"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$A$6:$A$8"
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$6"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$6:$C$6"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
ActiveWindow.SmallScroll Down:=3
Range("A11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='GALLERY ON 4TH'!$A$7"
ActiveChart.SeriesCollection(1).Values = "='GALLERY ON 4TH'!$B$7:$C$7"
ActiveChart.SeriesCollection(1).XValues = "='GALLERY ON 4TH'!$B$5:$C$5"
However I cannot automate. I tried different methods for creating the charts, as found on the site [prefer .chartobjects.add(x,y,z,a) since I can immediately position the chart] , but get stuck at seriescollection everytime.
It looks like when the chart is created by XL (with suggested data in it) I cannot address the series data.
I got it right to generate a chartobject that is empty, then added the series data to it, but when opening the new chartobject, it is again populated with suggested data.
How do I generate an empty chartobject everytime, so I can insert seriescollection info with code?
Any other way to generate charts?
View 9 Replies
View Related
Apr 11, 2007
am trying to create multiple pie charts now but I'm having problems my errors are highlighted in red. What I'm trying to achieve is to create a pie chart for the first table then another for the next table and so on but its not working out.
View 14 Replies
View Related
Jun 18, 2014
I have following code which is not behaving the way I want.
[Code] ....
The problem is the last statement, a chart which gets created in an iteration does not "move" to a new sheet but rather same sheet. So end result is a sheet with chart of the last iteration.
How to get this code to create new charts in new sheet and not the same sheet.
View 3 Replies
View Related
Oct 21, 2011
I've got quite of few excel charts to make and have been looking for a way to automate the process. The chart types include line, pie, and bar. Basically, I have data for each state in the US that I am comparing with national level data. Thus each chart will have national level data and the data for each state. What I need is a macro that I can easily change the parameters for because different worksheets have slightly different layouts. I started by using the macro recorder to make a chart. Then I attempted to edit it so that it would run loops to make additional charts. Below is some sample data for the first worksheet I have and my attempted macro.
Sample Data:
Code:
State Characteristic Value07 Value08 Value09
Alaska A 11 23 45
Alaska B 13 22 98
Alaska C 99 91 21
Alabama A 23 14 11
Alabama B 44 62 76
Alabama C 75 47 21[code]....
This code doesn't run at all.
View 4 Replies
View Related
Jun 28, 2006
Can i create a chart in excel just using a formula - but not data?
View 4 Replies
View Related
May 23, 2012
I'm trying to create a bubble chart created on three different columns of data. I'd like the following:
-The X-axis should just show the first columns of dates I have stored.
-The Y-axis should be the number of lots of a product that I bought of a certain product.
-I'd like the actual bubbles to not only represent the number of lots purchased on the given date, but I'd like the size to be represented by the third column, which is the average price of each lot.
Is there a way to do this? I've been trying to maneuver it around all morning, and can't seem to get it how I want it. I'm not sure if I'm ordering the columns incorrectly or what.
View 3 Replies
View Related
Mar 2, 2013
building dynamic charts and filters, so What I'm hoping to accomplish to build a bar chart that looks like the following:
________________________________________________
Filter: State | Filter: Region | Filter: Segment |
Revenues |||||||||||||||| + (% of Total Rev)
COGS ||||||||||| + (% of Total COGS)
Margin |||| + (% of Total Margin)
-------------------------------------------------------
My data is built in the following format on a different tab than the proposed chart:
State: | Region: | Segment: | Account: | Amount: |
MN | Midwest | Major | Revenues | $$$
MN | Midwest | Major | COGS | $$$
MN | Midwest | Major | Margin | $$$
MN | Midwest | Major | % of Total Revenues | %%%
MN | Midwest | Major | % of Total COGS | %%%
MN | Midwest | Major | % of Total Margin | %%%
[Code] ........
It seems like it'll be easy to use a pivotchart, but at the same time, there's a lot of formatting to do, which makes it tricky, especially if I have many states to deal with and the multiple combinations.
View 4 Replies
View Related
Jun 11, 2013
I have a dynamic set of data, loaded from a server. The columns are FIXED. I will attach a sample of how the data looks.
site_code
report_goup
asm_week
[Code].....
I don't know how to attatch a file here, but the first row is the title row, under it are the values. The columns here are fixed. What i NEED to do, is somehow create something that will make a seperate graph for every different PLATFORM , showing the FPY chaging over time. Time here is the weeks which will always be a rolling 12 weeks. I'm thinking that i need to use MACROS to do this, but i'm not sure where to start.
View 2 Replies
View Related
Jun 29, 2014
I want to find the number of similar values contained in a column based on a date range I specify.
I have a data set that's formatted like the following (you can see the full data set at [URL] but I've hidden the irrelevant columns and highlight the relevant ones)
[Code] .......
I want to use this data to create two types of graphs. One showing the ratio including all values in the Status column. There are 4 total (Open, Lost, Won, Won-Job Cancelled). I want another which excludes the "open" status from the ratio figure. I've create some images demonstrating what I want to accomplish at [URL] .....
Additionally, I want to be able to specificy date ranges for these charts. Specifically 0-30 days from todays date, 30-90 days and older than 90 days.
I want to accomplish this using Google Sheets (the latest version) and know how to use the graph total. It's just creating the data the way I need it which is proving to be the most challenging.
View 1 Replies
View Related
Apr 5, 2012
How I could create a chart (standard column) with an average line in Excel 2010?
View 4 Replies
View Related
Jun 27, 2013
My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers
Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average
When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.
View 2 Replies
View Related
Sep 5, 2012
I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.
Sub UWTierChart()
Dim oCell As Range
Dim oChart As Chart
Set ws_data = ActiveSheet
'Chart 1
Sheets(wsPT).Select
Range("B21").Select
[code]....
View 2 Replies
View Related
Dec 22, 2008
I am trying to build a selection for a procedure to use.
View 2 Replies
View Related
Jan 12, 2010
I have never done this before and I am having a hard time - it seems that none of the graphs in the Excel menu accommodate what I want and I have no idea what I am doing to boot.. Could someone PLEASE help?
My data is: American & Japanese workers can each produce 4 cars a year. An American worker can produce 10 tons of grain a yr. / Japanese 5 tons of grain a yr.
Each country has 100 million workers.
Need PPF and Opportunity cost of car & grain from U.S and Japan.
I have at this for two days and I cannot get the information in the chart the way it is supposed to be. It makes no sense evry way I have tried it and the graphs don't even look correct. I've tried several line graphs and scatter graphs..
View 11 Replies
View Related
May 23, 2008
I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....
What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)
As a lot of the time I use the same number range but on different rows and sheets.
View 12 Replies
View Related
Nov 26, 2008
I'm trying to find a way to select all charts in a worksheet and pasting them into a new sheet as a jpeg and can't find a way to select the charts all together as a group like if you shift click them.
I'm looking to copy and paste them as a group because they are grouped in a specific way for a report.
View 11 Replies
View Related
Oct 28, 2009
I have 2 charts on one sheet. Both the charts are generated using 2 different VBA code. I have also created a Button to trigger the code and generate chart when the button is clicked.
I recently realized that I was creating charts upon charts and was making the excel file bigger in size all the time.
So, I wanted to see if I can delete the individual chart before generating a new one.
Currently, I am using the following code to delete the charts.
View 4 Replies
View Related
Dec 3, 2009
I am having lots of issues with generating a scatter chart in vba. I tried recording a macro but the only thing that recorded was my different selection clicks on the chart. No formatting was recorded. I have the following ....
View 12 Replies
View Related
Jan 11, 2010
if marco recording of chart manipulation has been reintroduced in XL2010
View 6 Replies
View Related
Feb 17, 2010
I have a piece of code that arranges a chart to fit the data it should show. The code used to work perfectly in the old Excel version, but after an "upgrade" to Excel 2007 I get "Automation Error" from the line where the scale type is set.
View 5 Replies
View Related
Apr 21, 2013
I don't mind paying for the add-on/add-in, I need to have a chart displayed of 100 rows and 1000 cells of data.
View 6 Replies
View Related
Feb 24, 2007
I have a spreadsheet that calculates a value for each age up to 65 on a separate row. I have parameterized the spreadsheet so that depending on the age entered by the user, any row below the age 65 calculation row is blanked out (set to "") with an if statement.
After a new age is entered I would like a line graph to compare the values. My problem is that if I select all of the possible data values (I assume none are less than 20, therefore there are 45 rows of formulas) any rows that have blank values show up in the chart. My data starts in cell A1.
I would like to come up with a macro that accomplishes the following . . .
- determines the number of the lowest row that has a number value in it (since there are formulas below this, Ctrl down does not do this correctly)
- set a named range starting from cell A1 based on x rows (calculated in the prior step) and y rows (constant)
View 9 Replies
View Related
Aug 27, 2009
Excel / Word 2003.
I am exploring yet another avenue of vba coding that i am not familiar with.
I have a template word document that i am wanting to fill with items (be it cell values or charts) from an excel file.
I am looking at the coding examples on the net and i am slowly getting there. However i seem to be unable to copy a chart from my excel file and copy into word. I need to be able to specify a specific position on the word document too.
View 9 Replies
View Related
Nov 4, 2009
Has anyone seen this problem before;
I used a "MID" formula to extract some data and for some reason the chart I'm trying to use won't recognize the data. Here is a copy of what the data looks like. Non of the vaules will show up on the chart.
DPJE2DD2CE2CJan1.2784.8984.8997.65Feb1.2697.6597.6591.79B10==MID(B$3,8,5)Mar1.3091.7991.79154.7Apr1.28154.7154.7147.6May1.29147.6147.687.78Jun1.4387.7887.78112.6Jul1.39112.6112.6115.2Aug1.42115.2115.293.6 Sep1.3393.6 93.6 138.9Oct1.28138.9138.9NovDec115.6YTD1.32115.6115.6
View 9 Replies
View Related
Jan 12, 2010
How do I get my charts to update everytime, the data refreshens...I have my series linked to several worksheets within a workbook.
View 9 Replies
View Related
Nov 13, 2008
i am trying to add 50 new data series and it takes hell of a lot of time to do it manually.
Sub Macro6()
Dim total As Integer
Dim Taper As Integer
Taper = 2
Do Until total < 100
total = total + 1
Taper = Taper + 1
ActiveSheet.ChartObjects(" Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection("Total").XValues = "=Data!R" & Taper & "C6"
ActiveChart.SeriesCollection("Total").Values = "=Data!R" & Taper & "C8"
ActiveChart.SeriesCollection("Total"). Name = "=Data!R" & Taper & "C9"
ActiveChart.SeriesCollection("Total").BubbleSizes = "=Data!R" & Taper & "C7"
Loop
End Sub
View 4 Replies
View Related
Sep 29, 2004
I have lots of charts in a sheet "plots" which is based on data in sheet "data Summary". Sheet "plots" have the following code, which is based on the event "actiavte". At the end my main macro, I am activating sheet "plots" and all the charts are working fine. After this if I go to any other sheet in the workbook and come back to "plots", all the charts are flickering and the cursor turns to busy mode. I don't understnad why this is happening.
Private Sub Worksheet_Activate()
For Each ChtOb In ActiveSheet.ChartObjects
ChtOb.Chart.PlotVisibleOnly = False
Next
ThisWorkbook.Worksheets("plots"). Range("e1").Activate
On Error GoTo Handle
With ActiveSheet.ChartObjects("s21max").Chart.Axes(xlCategory, xlSecondary)
.MinimumScale = Application.Average([s21high]) + -4 * Application.StDev([s21high])
.MaximumScale = Application.Average([s21high]) + 4 * Application.StDev([s21high]) + Application.StDev([s21high]) * 0.25
.CrossesAt = .MinimumScale..........................
View 2 Replies
View Related
Jun 9, 2005
I need to export serveral charts to several slides within PowerPoint from Excel Using VBA. I use 2002 versions. I added chartobjects (2) under 1 but not sure if that is the correct placement to start adding more charts to slides.
Private Sub CommandButton1_Click()
'Sub PowerPointOLEAutomation()
Dim ppt As Object, pres As Object
'Create a Microsoft PowerPoint session
Set ppt = CreateObject("powerpoint.application")
'Copy the chart on the Chart Labels Demo sheet
Worksheets("sw dr").ChartObjects(1).Copy
'Worksheets("sw dr&ot").ChartObjects(2).Copy
'Create a new document in Microsoft PowerPoint
Set pres = ppt.Presentations.Add.........................
View 4 Replies
View Related
Jul 18, 2006
Excel Chart can fit various trendlines in a given set of data. For example we can get linear, as well as exponential trend line for same data set.
How do I decide which trendline best represents the trend in data? Shoud R Sq be used as parameter to see the best fit?
View 9 Replies
View Related
