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 RepliesI wrote a macro to create a chart out of a specific range of data.
View 11 Replies View RelatedHow 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 RelatedMy 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?
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.
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.
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 RelatedI'm trying to create a chart and set its source dynamically.
I've tried two methods, the first by setting the XValues of a chart's series collection to a range specified by cells,
ActiveChart.SeriesCollection(1).XValues = range(Cells(9, coloffset + 4), _
Cells(9 + numvsteps, coloffset + 4))
And by using the chart wizard.
Call vchart.Chart.ChartWizard( _
Sheets(resultssheet).range(Cells(9, coloffset + 6), Cells(9 + numvsteps, coloffset + 6)), _
xlLine, , xlColumns, _
Sheets(resultssheet).range(Cells(9, coloffset + 1), Cells(9 + numvsteps, coloffset + 1)), , _
False, "End-End Eff., " & current & "A", "Line Voltage", "Eff. [%]", "test")
Setting the XValues property gives an "Unable to set the XValues property of a series class".
Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.
I've also tried
resultssheet = "Sheet1"
tempstring = "='" & resultssheet & "'!" & "R9C" & (coloffset + 1) & ":R" & (9 + numvsteps) & "C" & (coloffset + 1)
ActiveChart.SeriesCollection(1).XValues = tempstring
When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.
Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.
I have various charts others view and require the data range to change in 12 month intervals, ie Jan07 -Jan08, Feb07-Feb08. I currently move the ranges manually every month but find the it would be more interactive for others if a drop down combo box on the chart were used enabling the user to choose which intervals to view.
View 5 Replies View RelatedCurrently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?
This is what I've written so far:
Code:
Sub ammend_datarange_series_all_charts()
Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer
[Code]....
As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.
I've read through every thread I can find about dynamic named ranges as source data for a chart. It seems as if everyone can get it to work but me. I have a chart on one sheet that needs to update dynamically from data on another sheet when a button is clicked. Here's my code that runs when I click the button:
Sub Update_Center_Chart()
Sheets("Center Data Chart").Select
ActiveSheet.ChartObjects("Center Data").Activate
ActiveChart.SetSourceData Source = Range("CenterData")
End Sub
I get the error message: "Type mismatch." The data is in B1:F2 on a different sheet. The formula in the named range, "CenterData" is this:=OFFSET('Center Data'!$B$1,0,0,COUNT('Center Data'!$B:$B),4). The data will expand by 1 row every time.
Hello, what I would like to do is take a portion of a sheet..... So the cells I want to mirror are in the proposal sheet cells B32 x F51. And then mirror the exact duplicate copy to another sheet. The other sheet should have those same values at A20 X G39. What I am trying to do is have a sheet that has skus, prices, and so forth and then when I add,delete, merge, color, or do anything to the cell on the "proposal creation" sheet I want it to mirror only a specific cells to another sheet that is "salesman copy" that we will print to clients. So this way ont he proposal copy a salesman can insert rows or even change the color of the cells and then have it mirror exactly the same on the "salesman copy" which is what we will print for clients.
View 9 Replies View RelatedI would like to mirror a range of cells between two sheets in the same workbook, so that if the data is manually input into one of the cells in range (E5:H11) in 'Sheet 1', it is automatically updated in the corresponding cell in (H33:K39) of 'Sheet 2', and vice versa.
Using Office 2013. T
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...
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.
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.
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.
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 RelatedI 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.
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 RelatedI 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?
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.
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 RelatedI'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.
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 RelatedI 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.
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...
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),
I am trying to write a macro that will automatically change the source data for a graph. This is an existing graph that is on tab GraphYTD and the data for the graph comes from DataYTD. The number of rows of data will change monthly, but I would like to use the same graph template every month. Here is my
Sub SortYTD()
Dim myBottom As Long
myBottom = Sheets("DataYTD"). Range("B65536").End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("DataYTD").Range("F2:I" & myBottom)
ActiveChart.Location Where:=xlLocationAsObject, Name:="GraphYTD"
End Sub
I am getting Runtime error 91 - object variable or with block variable not set.
For example, if my graph currently shows B1:L1 range, how by clicking a spin button to make the graph show B2:L2 range?
...