Format Multiple Charts Via VBA?
Jul 28, 2013
I 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
ADVERTISEMENT
Apr 21, 2014
I 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.
View 1 Replies
View Related
Jan 18, 2008
I want to conditionally format the fill colour of pie or donut segments based on either the value of the segment (e.g. red for negative, green for positive), or based on the value of related cells (e.g. Equal sized segments coloured red for related %age value >40, amber for 40 to 60, green >60).
View 5 Replies
View Related
Feb 14, 2008
I have a macro that charts data as line charts. I wish to format droplines and updown bars to different colors. The challenge is that some charts do not have upbars and others do not have down bars. Is it possible to skip? The formatting code I used was:
Sub Formatter()
With ActiveChart
With .ChartGroups(1)
.HasDropLines = True
.HasUpDownBars = True
.GapWidth = 90
End With
.ChartGroups(1).UpBars.Select
With Selection
.Border.ColorIndex = 5
.Interior.ColorIndex = 5
End With ........................
View 2 Replies
View Related
May 23, 2014
I have an excel which creates PPT from the excel. but the graph pasted in PPT are not aligned and formatted.
By aligned, i mean re-size the graph and paste in a formatted fashion (currently they are overlapping each other)
My entire PPT created is off alignment and formatting. E.g heading are not properly aligned (they are touching the line below) and so on.
View 1 Replies
View Related
Apr 14, 2014
The 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.
View 4 Replies
View Related
Aug 18, 2012
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]
View 9 Replies
View Related
May 11, 2009
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 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
Jul 10, 2008
I 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 .......
View 14 Replies
View Related
Mar 15, 2012
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] ....
View 1 Replies
View Related
Jul 6, 2012
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]
View 5 Replies
View Related
Mar 17, 2014
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 Related
Oct 22, 2007
I 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
View 9 Replies
View Related
Mar 17, 2009
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.
View 3 Replies
View Related
Jun 27, 2007
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.
View 9 Replies
View Related
Feb 8, 2014
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.
View 5 Replies
View Related
Apr 2, 2014
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] .....
View 1 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
Aug 28, 2013
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?
View 3 Replies
View Related
May 9, 2009
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).
View 2 Replies
View Related
Dec 20, 2007
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 Related
Jan 18, 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 Related
Jun 26, 2008
I 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..........................
View 2 Replies
View Related
May 22, 2012
I have a file with data in one sheet (unfortunately I cannot share the file because of confidential data..), and two sheets with both 9-11 charts in them.
The charts are all filled simply with a dynamic range from the datasheet, so that Last Year and all months from this year with data in them are always in there.
The problem: When opening either of the graph sheets, the (familiar?) error pops up: "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name and cell reference."
One of the graphs has an error and only shows one point of data in it. But only until I press F9, and then the chart is repaired and functions perfectly like all the others!
I have tried lots of things, including checking all source ranges, deleting all graph names etc. and then repaired it so it would work again. The only thing that happened, is that the same error now pops up for another graph in the sheet. Still, when you press F9, the graph functions again!
View 6 Replies
View Related
Aug 5, 2008
Is there a way to make multiple charts off of 1 big data sheet. Sounds easy but there is more.
Sheet name is Field
Column A is Name ( about 40 different names)
Column B is Date (x axis primary and secondary)
Column D is Oil (y primary axis)
Column G is Water (y primary axis)
Column E is Gas ( y secondary axis)
Column M is MCFE (y secondary axis)
There is about 2-3 thousand rows, updated monthly. I need to make a graph by each well name.
1) can I make multiple graphs that can just look at the "field" sheet and then breakout just the 1 well name that shows up on the chart sheet that I create
I've tried pivot charts but you can't have secondary acess because it resets every time.
Basically, make multiple charts that will graph the data of a specific name in column A
View 9 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
Sep 18, 2008
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
View 9 Replies
View Related
