Chart Without A Specific Source Range

Sep 1, 2009

I wrote a macro to create a chart out of a specific range of data.

View 11 Replies


Define Chart Source Range

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

Change Chart Source Range

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

Expanding Range For Chart Source

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

Mirror Chart Source Range

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

Source Chart Range Until Empty Cell

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

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

Changing Chart Source Data Range

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

Dynamically Update Chart Source Range

Aug 31, 2007

I'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.

View 7 Replies View Related

Adjust Chart Source Range Via Combo Box

Mar 31, 2008

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 Related

VBA - Retrieve Chart Series Source Data In Range?

Jan 13, 2010

Currently 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:

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


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.

View 4 Replies View Related

Dynamic Named Range As Chart Source Data

Jun 16, 2008

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.

View 5 Replies View Related

Place Chart On Specific Range Macro

Aug 1, 2007

I'm working on an assignment that pulls information for different countries and I'd like to know how to go about placing the charts on a specific area. Any help would be greatly appreciated!

The concept:

With each click of a country, the country's information will be pulled from a 'data dump' that I created in the same file. The map and flag are also shown. Some of that information is just using lookups, or a combination of lookups and code, and the hardest part are the GRAPHS - which I'm having a really hard time placing and adjusting accordingly.

The problem:

As of now the charts are automatically filled as the user clicks a new country, but all of the charts are placed in weird locations, but I'd like for them to be placed in the specified ranges above.

I would like charts in the following ranges:

A20:B30 (Immigrant Group)
D20:E30 (Ethinc Group)
F20:G30 (Languages)
F13:G19 (Religion)
D10:E17 (Age structure)

This is the major part of my project that I need to complete. Any help would be greatly appreciated, PLEASE. =)

Right now I have ranges that aren't accurate, but regardless of how I try to show my range, the function does not work:

Sub CreateCharts()
Dim counter As Integer, chartname As String, xvals As String, offset As Integer, Range As String

'Delete all charts
Dim oc As ChartObject
For Each oc In ActiveWorkbook.ActiveSheet.ChartObjects
Next oc

View 6 Replies View Related

Macro To Copy Specific Cells From Row From Source & Stop When Next Row Cell = Specific Value

Nov 7, 2008

I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)

After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.

Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.

Sheets("ee template").Copy After:=Sheets(3)
ActiveCell.Offset(0, -11).Range("A1").Select

View 9 Replies View Related

Each Chart Source Row As A New Series

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

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 _
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 View Related

Set Source Data For Chart Using R1C1

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

Manipulating Chart Source Data With VBA

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:

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

Chart Not Reflecting Data Source

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

Chart: Selectable (ddl) Data Source

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)

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

Chart With Changing Source Data

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

Changing A Chart Source Sheet

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

Dynamic Chart Source Data

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

Change Chart Source Data From Add-in

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

Automate Chart Source Update

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.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

Change Chart Link Source

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

Create Dynamic Chart From Source Table?

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

Enable Editing Chart But Not Source Data?

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

Chart Source Data: Union Of Every N Cell

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
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

Graph & Chart: Data Source Changes Monthly

Nov 19, 2006

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.

View 3 Replies View Related

Link Spinbutton To Chart Data Source

Nov 21, 2006

For example, if my graph currently shows B1:L1 range, how by clicking a spin button to make the graph show B2:L2 range?


View 9 Replies View Related

Copyrights 2005-15, All rights reserved