Changing Chart Source With Vba
Jan 31, 2007
I'm trying to create a chart with different source ranges e.g if i used
With ch.Chart
.SeriesCollection.Add _
Source:=Worksheets(1).Source:=Worksheets(1).Range("C1:C8,J1:J8")
End With
That works fine but I want to modify the columns it looks at. I was going to use the cells(x,y) method but I can't get it to work. icol = 3. Range(Cells(1, iCol), Cells(8, iCol)). This works for a single range(C1:C8), but how do i reference Range("C1:C8,J1:J8") in the same way.
View 3 Replies
ADVERTISEMENT
Aug 11, 2006
I am trying to create a macro which will allow me to change the source data for a particular chart. what i want to do is to have a code which looks in a range of cells (A8:B28) and then updates the chart to only include those cells within the range that contain a value? The number of cells with values changes depending on what options are selected elsewhere in the spreadsheet.
View 2 Replies
View Related
Feb 9, 2007
I have a sheet ( Graphs A) with 50+ graphs on all linked to a datasheet (Data A).
I now want to copy these graphs to another sheet (Graphs B) which links to a different data sheet (Data B). The data is in the same layout format etc.
The problem is that the find-replace function will not replace the sheet name in the charts series formula from Data A to Data B
Is there an easy way to change the source sheet for the new graphs?
View 3 Replies
View Related
Nov 30, 2006
Each week I have to add a new column to a report and then update the source data of all the charts that use that newly added column. I am trying to create a macro that will use the last 12 columns of data located on a different worksheet as the range for the source data of the chart. I have attached an excel document that contains an example of how one of the charts that I am using looks and where it pulls the data from.
View 3 Replies
View Related
Mar 5, 2013
I have data. 2 columns a2:b5
each row has a different font color
apple 4
banana 3
peach 2
pear 1
I want the label in a chart to march the font color of the source cell
So in a chart I'd have 4 labels:
"apple"
"banana"
"peach"
"pear"
I can't see how to do it. When i select the albels it's "all or none". I can change ll the labels to a color. But, I want each individual label to be a diferent color.
View 3 Replies
View Related
Apr 4, 2007
I currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ...
Y-Axis is the data that I am trying to present - Total $
The source of this data is simply two columns - Column A is the running date, Column B will be data.
Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily).
Can a macro be used to auto update the bar charts based on today's date?
View 9 Replies
View Related
Oct 16, 2006
I've a set of excel sheets reading from a single excel data source, the only problem is that the data source is constantly changing, for example:
=DCOUNT('T:GreenbeltProductivity ToolWeek 42[Dept Overall and Data Entry.xls]Insert Data - Current Position'!A:F,5,AC139:AD140)
The week number is the only bit of the code that needs to change week on week, to update the data source on all the sheets and I'd like to password protect the data source.
View 5 Replies
View Related
Feb 11, 2014
I have a workbook with some pivot tables connected to a single slicer. My macro needs to disconnect the pivots from the slicer, change the data source, and reconnect the slicer. When my code gets to reconnecting the slicer it bugs out as if the data source is not the same for all the pivot tables (also doesn't let me reconnect manually). But when I look at the source, it is all the same and correct. If I manually set the data source the slicer reconnects just fine. Why is my code causing problems with the slicer?
Here is my code for setting the data source. Pretty sure this is the code that is causing problems for me.
Code:
newaddress = "$B:$" & Mid(Cells(1, YTDcolumn).Address(), 2, 2)
.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="TOP250Excel!" & newaddress, _
Version:=xlPivotTableVersion14)
Here is my code for reconnecting the slicer connections.
Code:
ActiveWorkbook.SlicerCaches("Slicer_Top250Excel_cypheren_Query_Name"). _
PivotTables.AddPivotTable (.PivotTables("PivotTable1"))
View 1 Replies
View Related
Jun 2, 2008
I'm trying to change the source data of an existing PivotTable so that it is linked to a raw data source in another workbook. The workbook it pulls the data from changes every month so I made this flexible code that is meant to pull the raw data every month:
Sub ImportNewSource()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FilePath As Variant
Dim ThisPivot As PivotTable
Dim FileName As String
Dim ShtNum As Integer
Dim LastRow As Long
The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.
When I do it manually, it works just fine. There aren't any changes to the PivotTable field or anything like that so I'm stumped. I've tested the range that has the source data and its selecting the proper range and when I do it manually it works fine.
View 9 Replies
View Related
Jul 19, 2012
My question is regarding changing properties in a chart without activating it. My current example is with adding data labels, but there are many other instances I could use this information in. Here is my current code:
VB: ActiveChart.SeriesCollection(2).ApplyDataLabels
However, I was hoping to replace it with something like this:
VB: Sheets("Dashboard").ChartObjects("Chart 1").SeriesCollection(2).ApplyDataLabels
I get the "Object doesn't support this property/method" error. Is there a way to do this? It just seems inefficient to have to activate the chart in order to make changes.
View 2 Replies
View Related
Dec 13, 2006
my problem is:
I have a whole list of data which changes according to the users preferences
It comprises of a NAME, X Value, and Y Value and the addition of X and Y. (these all have auto filters on them)
The user inputs under these fields, as many rows as they wish (which will be around 20ish)
I want it so when i click a button "CREATE GRAPH"
it selects the highest 10 values (if there is 10 values, if not then all the values) from the 4th column (X+Y) and then creates a graph. i want each row to be a new series...
View 9 Replies
View Related
Apr 24, 2014
I am trying to monitor the status of a cell on another sheet and autopopulate a cell depending on that information. The formula works well until I give the spreadsheet to a 3rd party and the formula ranges change after they paste new data to the source sheet. I have tried locking and password the formula cells but they change range regardless!
Here is the formula from the first cell.
[Code] ......
It scans for a number in an adjacent cell. If the number is present on the sheet 'Test', it autopopulates the cell with a string from the source sheet. The cell remains blank until there is data present.
If I cut data from row 6 and paste it to row 17, the formula cahnges itself to:
[Code] ........
How I can lock down this formula so that the ranges remain the same i.e. $AT$6:$AT$26, despite changes on the source sheet? I have tried F4 to toggle relative and absolute references and this has made no difference.
View 4 Replies
View Related
Mar 9, 2014
Everything else in my macro is using R1C1 format for ease of automation. However, I cannot get this to resolve. I am setting the source data for a line chart.
However, if I use A1 format it works just fine.
I am using R1C1 for automation purposes. A1 format will not easily work. I have the interpolated statement in there because the range contains some empty cells. I want excel to fill the line in the chart using interpolation. All the data in the columns(N through R or 14 through 18 in R1C1 format) is contiguous. I have not tried Union, but it seems I should just use this range like I do in the A1 format.
View 6 Replies
View Related
Sep 1, 2009
I wrote a macro to create a chart out of a specific range of data.
View 11 Replies
View Related
Aug 8, 2007
I have a chart that I am trying to set up with dynamic data. I have the chart created, but the problem is that I will have a variable number of categories based on the data that is loaded, so I need to use VBA to change the range for the source data and labels on the fly (otherwise, I will end up with 20-50 categories that are all zeroes, putting a bunch of gobbledegook at the top of the chart).
What commands to I need to enter into VBA to get this to work?
I have put a formula in 2 cells to determine based on the current data what the source data values range should be, and what the category labels range should be.
The cell that holds the value range is Graphics!L6, and the cell that holds the labels range is Graphics!L7.
The current values that these cells hold are:
L6:
=Graphics!$J6:$J9
L7:
=Graphics!$K6:$K9
Oh, and the name of the chart is "Chart 1"
What do I need to put into VBA to get the chart to change the data source values and category labels to the ranges I have listed in those 2 cells? (and for that matter, have I listed the ranges correctly in those 2 cells so that they are usable?)
If possible, I would prefer to do this without having to use code to select the chart, because I want the updates to the chart to be invisible to the user.
View 9 Replies
View Related
Jun 29, 2006
In my table I have rounded my values to a particular number of decimal places. BUT, the bars of my chart have the length of the original numbers. This is especially ridiculous when in the table there are only (rounded) zeros, and in the chart I get several bars with different length. how to plot the bars in the same length as the numbers in the table are?
View 9 Replies
View Related
Jul 22, 2006
How can I take the source data of a chart to be all those cells in the column, that begin with the number "1"?
View 2 Replies
View Related
Aug 10, 2006
I have a sheet with logged test data.
Various test items are stored in columns.
(First row of each column is the description of the test item)
Problem:
1) To reflect certain testdata in a chart I want to be able to select a different column (Y values) by means of a dropdownlist.
X values are just the number of items (1 to ...)
How to make the charts data source selectable?
2) It seems that the input range of a ddl should be a list of items.
In my case they are in the first row of each column so only the first item is shown.
View 5 Replies
View Related
Jun 14, 2007
My spreadsheet has multiple charts, each referencing a dynamic named range as its source data (e.g. "California1", "California2", etc). Each named range contains multiple series. I would like to make it easy for a single chart to quickly adjust to display different named ranges (e.g. view the chart with "California1" data, then update the same chart to view "California2" data).
I'd like to do this without having to right-click on the chart, select Source Data, and then type in "California2" as the Data Range. My question: is there a way to make the chart's Source Data Range dynamic so that I can quickly select the named range that I want to display? For example, would it be possible to tie the Data Range to a separate cell so that if I typed "California2" into that cell the chart would know to use that named range as its new source data?
View 7 Replies
View Related
Jul 6, 2007
I have a problem setting up an offset range on my charting example ( attached).
My X axis will always be row A
I want my charting range to be more than just the one column and since the months will keep expanding I'm looking fopr the easiest method to do this.
My range could start at 1 row and finish at any other. User Defined.
Bu I want that whole range to be included in the charts. This is a stacked chart.
So in my example I'd liek the shaded bit as a stack chart. 3 bars for 3 months with 4 stacks in each one.
View 9 Replies
View Related
Jul 27, 2007
In a workbook that I am working on, a macro runs to import data onto 9 separate sheets. The data is essentially x- and y- coordinates of a plot profile from another program. The problem that I am running into is that the number of data points can vary each time the data in imported based upon how the data was acquired.
If I were to put a drop-down list (Method1, Method2, Method3, etc.) on the Instructions page that I have for this workbook and have the user select which method was used prior to running the macro, could I then incorporate some sort of IF statement in the macro to vary the Source Data Series for the charts that I have created? Right now, I have the Source Data covering the scenario with the longest list of data points so when fewer points are used, the graph looks essentially useless.
View 5 Replies
View Related
Aug 9, 2007
I have an excel addin that contains chart templates. Without going into too much detail a user will select an option which will update these templates then move the charts along with the source data to a new workbook. My question is this; How do I change the chart references programatically so that the chart data now points to the source data in the new workbook.
View 5 Replies
View Related
Aug 30, 2007
I'm trying to change the source data for a worksheet for a series of charts. The source data appears in a series of tables. There are about 30 such tables and associated charts on the worksheet. (I need to duplicate this many times over, which is why I need to automate.). The chart names seem to increment by 2 (i.e. Chart 1, Chart 3, Chart 5) And the source data for the charts increment by 9: BM95:BP102, BM104:BP111, etc. How can I automate this? Also, I'm trying to automate changing the reference in the title of the chart. This reference increments rows by 9. When I use the macro recorder to do this, I get the following
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Range("BM95:BP102")
I'm unable to generate any code that allows me to change the reference of the chart title.
View 4 Replies
View Related
Apr 18, 2008
I have a periodic task which involves moving some sheets from an existing workbook and presenting it in a new workbook. There are some diagram that I move as well from the worksheet. However, I also move the source data for the diagrams into the new workbook as well. Everytime I do this I need to change the source data to the new worksheet which is quite tiresome. Is there a smart hack to actually point the diagram source data to the new workbook?
View 2 Replies
View Related
Jun 17, 2008
How can I write a macro to mirror x data that will be charted using xy scatter? The original data is entered into excel using measuring equipment with a footswitch. I need excel to automatically mirror the incoming data as it comes in. It should look like my example only with 2 more columns to the right with mirrored x data in the 1st and the same Y data in the second. Example.xls
View 9 Replies
View Related
Mar 5, 2013
I have the following code to update a pivot table:
Code:
Dim pt As PivotTable
Application.EnableCancelKey = xlDisabled
For Each pt In ActiveWorkbook.Worksheets("sheet1").PivotTables
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:="source!R4C1:R33443C55" _
, Version:=xlPivotTableVersion10)
Next pt
the R33443 term is what will be changing, the columns and the starting row should stay the same. is there a way to instead of using R33443, to enable the range to be changed based on a cell value?
View 2 Replies
View Related
Jan 16, 2013
I am writing the following code to set the source data for a column chart. the source should be F13 until last cell, which is F862
VB:
ActiveChart.SetSourceData Source:=Sheets("Input TE country").Range("F13:" & ActiveSheet.Range("F13").End(xlDown).Address), PlotBy:=xlColumns
Now it selects F13 until the last cell, which is F65536.
View 5 Replies
View Related
Apr 17, 2014
I have a table of company data, and I want to be able to select different companies data and create a chart automatically perhaps by pressing a 'create' button.
The table data contains three values I want to populate in the chart: red & green sales which I would like shown as a stacked bar, and red as a percentage of total sales which I would like on a different axis as a line.
I tried to create a macro but my VBA skills are almost non-existent.
View 2 Replies
View Related
Mar 18, 2014
is it possible to unlock a chart but then disabling to change the source data?
Background: I want the user to be able to add numbers etc. but he shouldn't see the underlying table...
View 2 Replies
View Related
Sep 19, 2006
I have 4 sheets. In each sheet, I have a column of data for every hour of every day of one year.
In the 1st worksheet, I want to make a chart using :
- for Y values : the average of the data for each day
- for X values: the day
In the 3 other worksheets,
- for Y values : the data for each hour
- for X values: the hour
To make it more simple, I will first build in the "D" column the data of the average data for each day (so, every 24 columns)
Here is my
Dim lNbSheet As Long ' sheet number
Dim rSheetData As Range ' what will be my Y values
Dim rSheetTime As Range ' what will be my X values
Dim sSheetName As String ' current sheet
For lNbSheet = 1 To 4
'selection of the Y values
Sheets(lNbSheet).Select
sSheetName = ActiveSheet.Name
I get the error "methode SeriesCollectiosn of Object _chart failed;
Also, if you have an idea that could make me avoid writing all the new average-for-the-day data in column D (using it directly),
View 8 Replies
View Related
