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 Repliesbuilding 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.
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.
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.
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?
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.
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.
Can i create a chart in excel just using a formula - but not data?
View 4 Replies View RelatedI'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.
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.
How I could create a chart (standard column) with an average line in Excel 2010?
View 4 Replies View RelatedThe macro creates multiple charts from data in one sheet, but now when I'm trying to scale it up to multiple sheets, the previous charts created disappear. E.g Creates 5 charts for sheet 1 (can see them being created, using debugger mode), looping to the next chart and they are created but the first ones has disappeared! What am I doing wrong? I'm creating my chart by the following code:
[Code] .....
The startpoint variable is changed for every new chart within the one sheet and the outputsheet is changed when you change the sheet.
I'm trying to create multiple Charts with VBA, each chart on a seperate Sheet. The link below works but i need to change it some and i'm stuck. What i'm trying to do is create a new sheet for each new graph, but I need it to create a graph for the 250 associates in column B then plot 52 weeks of their % from the Row their name shows up in. First name is in (B3), his % data is in range (N3:BO3). I have the weeks numbered out 1-52 in (N2:BO2) and i'd like their name to appear in the Tab and on top of the graph. I applied the VBA code from the link below to my sheet and changed what i knew i needed to change, but it debugs. I use to do each graph manually when i had 25 associates but i would like to use this for the whole building now.
[URL]
I created a pivot chart and am utulising the report filter. Is there a way to display multiple charts on the same worksheet (or even another worksheet) for the purpose of comparison?
View 4 Replies View RelatedI have 23 Charts on a sheet which I place into a MS Word Doc. ATM all charts have borders, which is what one of my Managers like, however another Manger likes these charts without borders. I tried to record a macro so I could have this done by VBA (excel 2007) but it did not record the formatting. write a code to remove a border and and then add a border back in specifying colour, border styles (width etc). Say just for Chart 38, I can the just repeat this. But here are all the chart numbers in case you need them: 38,20,27,5,11,21,13,6,9,19,10,7,28,29,30,31,32,12,33,34,37,35 & 36.
View 5 Replies View RelatedI have a workbook with a chart on a worksheet called Area Report
There are 10 areas in total A - J, to view a particular area cell I2 is validated to only accept entries A - J which represent the Areas, selecting a particular area updates the chart for that area
Is it possible to have a macro that will print all 10 Area Reports, I tried to do it using the Record Macro function, the code was as follows .......
I am working on a huge amount of data and want to analyse that data through charts because charts are quick representation of the data and save a lot of time. I got some code from my friend, to read each row in a worksheet and plotted the corresponding chart on the new worksheet each time i.e., each row has one chart which is plotted on new worksheet each time.
But I don't want it to plot the chart on the new worksheet. I want it to plot the chart on the same worksheet. In my case there are always more than 30 rows, so it should draw 30 charts on the same worksheet in which 30 rows are written.
Here is the code which plots charts on the multiple sheets
Code:
Sub LineCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long
Set Ws = ThisWorkbook.Worksheets("Sheet3")
LastRow = Ws.Range("A65536").End(xlUp).Row
[Code] ....
I would like to generate a macro which can work on by importing an excel tabulated datas and plot it into several graphs in one click and added with a filter function would be best.
Here i link one of the sample of macro for this :
[URL]
I have two separate data tables (with separate named ranges) and two separate pivot tables (the pivots each refer to the separate tables) all on the same sheet. For each of the pivot tables, I would like to have a separate pivot chart. In the pivot charts, when I click "Select Data", I find that the "Chart Data Range" for the pivot chart is grey (uneditable). Is there some setting that I need to toggle to enable the data source range so that I can change it? Also, when I try adding a new pivot chart to the sheet, this option is also grey/unavailable.
View 1 Replies View RelatedI have used named ranges to make a chart automatically adjust to a changing list of products in one column, but I do not know how to do this with multiple series. If I want to add products to this table, how can I make my chart dynamically adjust multiple series?
So the chart range is currently E5:H10. But I would need it to go beyond row 10 when I add new items.
Sheet1
*EFGH5*Week 1Week 2Week 36Product 11,234,123 1,258,805 1,283,982 7Product 21,234,223 1,258,907 1,284,086 8Product 33,423,321 3,491,787 3,561,623 9Product 4342,121 348,963 355,943 10Product 52,343,424 2,390,292 2,438,098
Excel tables to the web >> Excel Jeanie HTML 4
I'm using Excel 2007 and my objective is to setup a trading log that tracks my performance with charts that dynamically update to applied custom date range filters. The link below shows screenshots of what I'm trying to emulate with Excel 2007.
[url]
I have all my trading data laid out in a tabular format simlar to what is shown in the top screenshot. My tabular layout differs in that it includes 4 additional columns. My chronological data layout I'm using seems to be troublesome for conventional Excel charting methods.
When I apply custom filters for open dates and close dates, my Excel charts do not dynamically react. What I have determined is that I need to bypass the conventional charting methods used in Excel and focus instead on using Charts that rely on a VBA script or a formula. My objective is to have multiple charts (like the ones shown in the screenshots link above) all using the same data nested in my tabular trading log.
Can anyone out there help me out with setting up Charts that are more robust? I would appreciate example VBA scripts and / or links that will show me how to setup charts that dynamically react to custom Excel Filters, and can group together matching data sets in a column and show such data sets as a single slice in a pie chart. Currently, I'm getting multiple pie slices for the same data set. I need a formula or a VBA script that will clump together all of the data that belongs in the same group and show it as a single slice in a pie chart.
I have a chart that I want to be the same across multiple worksheets. The data ranges don't move, but the data may be different. It is cumbersome to go and retype the name of the sheet every time this chart is placed.
I have tried using named ranges. My named range X is !$A$1:$A$30 so that it will refer to the active sheet. If I place this in cells on the spreadsheet, it works. If I place "=X" in the values entry for the source data of the chart, I get a formula error.
I would like to use multiple scatter plots and bar charts in one chart. Problem is that X-Axis goes horribly wrong every time, even if I have same points on both scatter and bar. Here is Data for 2 Scatter Plots
XScatter1
1,43 0,14
2,60 0,25
3,04 0,37
3,60 0,50
3,93 0,60
4,69 0,80
X Scatter2
5,10 0,92
5,36 1,00
6,43 1,26
7,60 1,53
8,19 1,65
8,79 1,77
9,69 1,93
And here is data for 2 Bar Charts
XBar Chart1Bar Chart2
1,43#N/A 0,00
2,60#N/A 0,01
3,040,01 0,01
3,60#N/A 0,01
3,93#N/A 0,01
4,69#N/A 0,00
5,100,02 0,01
5,36#N/A 0,01
6,43#N/A 0,02
7,600,04 0,02
8,19#N/A 0,01
8,790,11 0,02
9,69#N/A 0,01
I start with Scatterplot and put those in the graph, then I add other data and change it to Bar Chart, but this is the point where X-Axis gets mixed up and scatter plot X data and Bar chart X data are not matching.
I am having an issue with looping through data ranges. Below is a subset of my macro. What I require is for LegendRng to stay the same and DataRng to move after each loop.
However everytime the vba runs through a loop, the previous range is recorded i.e. the source data for the chart is "A1:D5" instead of "A1:D1, A5:D5").
[Code] .....
I have about 9 different sheets, but they all will have 4 types of graphs (totals, comparison, bydate, trend). I am writing vba to conditionally show one type of graph on every sheet. For example, if I want to show the totals graphs, I want all of my sheets in the workbook to update. I can already make the graphs go invisible and visible on one sheet, but I would like to do so on all sheets. Here's my code now:
Code:
Sub UpdateGraph()
Sheets(".graphManager").ChartObjects("Totals").Visible = False
End Sub
[Code]....
But no luck. I do not want to manually type all the sheet names into an array because I may add more sheets in the future and don't want to keep changing the code. How can I loop through all sheets and set a graph named "Totals" to invisible? Or can I just set all graphs in the workbook named "Totals" to invisible without looping through the sheets?
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want:
Sub Macro5()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveWindow.SmallScroll Down:=-3
End Sub
How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
I have a spreadsheet with multiple pivot tables and graphs. Is there a way to add a scrollbar on one sheet so that when you scroll through it, it will display(one by one) each graph that I created with the pivot table(s) so that they're all on one page(still dynamic) as opposed to them being on multiple worksheets
View 2 Replies View RelatedI can run this code successfully:
Code:
With ActiveSheet.ChartObjects("Chart 2").Chart
.Axes(xlCategory).TickLabels.Font.Size = 20
End With
But this code throws a "This Object Is No Longer Valid" error when it gets to the first .Axes line:
Code:
Sub ChartFormat()
'
' Format Charts macro
'
Dim ch As ChartObject
For Each ch In ActiveWorkbook.Sheets("Summary").ChartObjects
With ch.Chart
.Axes(xlCategory).TickLabels.Font.Size = 16
[Code] .......
I'm using Excel 2007.
I'm trying to create a chart that will display dates along the vertical (y) axis and time (on a 24-hour timeline) on the horizontal (x) axis. Ideally, I'd like the chart to show the various time entries on each date, perhaps represented by a point or other mark at the appropriate intervals corresponding to the time entries for each date. Failing that, a Gantt-style chart that shows a span, represented by a bar beginning at the earliest time and extending to the latest time entry for each date would be useful....
View 9 Replies View RelatedI need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have:
Sub Graphs()
Dim Startpoint As Integer
Dim Endpoint As Integer
Dim count As Integer
Dim xStart As String
Dim xEnd As String
Dim NumberSets As Integer
Dim yStart As String
Dim yEnd As String
Dim DataSet As Integer
Dim Data
Startpoint = 11 'The first set always starts in row 11
Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10
NumberSets = Range("L7").Value 'number of times I need the loop to work
count = 1..........................