Pivot Charts Versus Charts
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
ADVERTISEMENT
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 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
Sep 16, 2009
pivot chart automation. I have a tab that contains thousands of rows and it grows larger on a weekly basis. On this tab is 169 peices of hardware I manage. Each week 1 row of data is added to all 169 peices of hardware. The data from the main tab contains metrics I need to chart. However, I am in need of a way of selecting a specific peice of hardware and having the pivot charts update automatically. There will be 26 Charts and going through each chart and selecting the proper hardware is going to take far too long.
Is there any way i can select just 1 peices of hardware from the data tab and have it populate all of these charts?
View 9 Replies
View Related
Aug 19, 2009
I have some monthly figures that's I need to put into a charts, and I want separate charts for each months data. People add to the list of figures almost every day.
Do I need to put the data in different sheets (Aug, Sep etc), with different tables and charts, or is the a way you can keep it all in one list, and filter it so each months figures get populated in their own chart?
I did try putting the data in separate sheets and making new charts for each one, but it made my file really big. Also as some months are yet to come, the blank sheets were making the charts mess up.
View 12 Replies
View Related
Feb 6, 2014
I am trying to find a way to write a code in VBA to conditional format Bars on a pivot chart to change colors based on if the field beats the forecast field.
I am attaching the sheet which explains it better.
I would like the bar series in the chart to show green if it beat forecast and red if it didn't.
I want the macro to do this for all the pivot charts.
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
Dec 15, 2006
My column chart is produced by a pivot table & pivot chart and then I manually select each regions code, and paste the associated graph into word 120 times over. This is extremely tedious.
Although there is facility for SHOW PAGES on a pivot table, I don't seem to be able to find an equivalent built in function for pivot charts.
I've tried locating my pivot chart on the same page as my pivot table and then hitting show pages, but it only reproduces the pivot table.
If I could even get a graph on each tab for each region - I could at least PDF the excel file as a complete report by selecting all the tabs and printing them into a single PDF file.
View 9 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
Mar 20, 2012
I am using some Pivot charts, but I want to override what legends calls particular lines. Is there any way to control what legend says?
View 1 Replies
View Related
Mar 15, 2013
I have a Reporting workbook I designed. There is a "Parent" workbook with the following design.
It has 6 Pivot Tables on a Sheet called "Data"
It has 6 Pivot Charts that were created from these tables on a page called "Summary"
These 6 Pivot Charts are all linked through a pair of slicers.
There is a "control" page which has instructions and buttons that trigger "Refresh All" and "Create Child Workbok".
This design is so that someone unfamiliar with excel could conceivably create the final product.
Essentially I have written/put together VBA to Create a new workbook "child" which has should be a funcitoning copy of the "parent".
The problem is the copiedmoved (I have tried both) Pivot Charts no longer update/refresh when the pivot tables change. I have script that reconnects the Pivot Tables to the Slicers. Slicers are fully funcitonal. Links in cells are fully functional.
Some of this code might look familiar.
Code:
Sub createWB()
' Copies VBA modules, Calls
Dim wbNew As Workbook, wbT As Workbook
Set wbT = ActiveWorkbook
On Error Resume Next
Kill ("PATHmod1.bas")
Kill ("PATHmod2.bas")
[code].....
View 2 Replies
View Related
Jan 12, 2010
I have never done this before and I am having a hard time - it seems that none of the graphs in the Excel menu accommodate what I want and I have no idea what I am doing to boot.. Could someone PLEASE help?
My data is: American & Japanese workers can each produce 4 cars a year. An American worker can produce 10 tons of grain a yr. / Japanese 5 tons of grain a yr.
Each country has 100 million workers.
Need PPF and Opportunity cost of car & grain from U.S and Japan.
I have at this for two days and I cannot get the information in the chart the way it is supposed to be. It makes no sense evry way I have tried it and the graphs don't even look correct. I've tried several line graphs and scatter graphs..
View 11 Replies
View Related
May 23, 2008
I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....
What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)
As a lot of the time I use the same number range but on different rows and sheets.
View 12 Replies
View Related
Nov 26, 2008
I'm trying to find a way to select all charts in a worksheet and pasting them into a new sheet as a jpeg and can't find a way to select the charts all together as a group like if you shift click them.
I'm looking to copy and paste them as a group because they are grouped in a specific way for a report.
View 11 Replies
View Related
Oct 28, 2009
I have 2 charts on one sheet. Both the charts are generated using 2 different VBA code. I have also created a Button to trigger the code and generate chart when the button is clicked.
I recently realized that I was creating charts upon charts and was making the excel file bigger in size all the time.
So, I wanted to see if I can delete the individual chart before generating a new one.
Currently, I am using the following code to delete the charts.
View 4 Replies
View Related
Dec 3, 2009
I am having lots of issues with generating a scatter chart in vba. I tried recording a macro but the only thing that recorded was my different selection clicks on the chart. No formatting was recorded. I have the following ....
View 12 Replies
View Related
Jan 11, 2010
if marco recording of chart manipulation has been reintroduced in XL2010
View 6 Replies
View Related
Feb 17, 2010
I have a piece of code that arranges a chart to fit the data it should show. The code used to work perfectly in the old Excel version, but after an "upgrade" to Excel 2007 I get "Automation Error" from the line where the scale type is set.
View 5 Replies
View Related
Apr 21, 2013
I don't mind paying for the add-on/add-in, I need to have a chart displayed of 100 rows and 1000 cells of data.
View 6 Replies
View Related
Feb 24, 2007
I have a spreadsheet that calculates a value for each age up to 65 on a separate row. I have parameterized the spreadsheet so that depending on the age entered by the user, any row below the age 65 calculation row is blanked out (set to "") with an if statement.
After a new age is entered I would like a line graph to compare the values. My problem is that if I select all of the possible data values (I assume none are less than 20, therefore there are 45 rows of formulas) any rows that have blank values show up in the chart. My data starts in cell A1.
I would like to come up with a macro that accomplishes the following . . .
- determines the number of the lowest row that has a number value in it (since there are formulas below this, Ctrl down does not do this correctly)
- set a named range starting from cell A1 based on x rows (calculated in the prior step) and y rows (constant)
View 9 Replies
View Related
May 26, 2009
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.
View 9 Replies
View Related
Jul 14, 2009
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?
View 9 Replies
View Related
Aug 27, 2009
Excel / Word 2003.
I am exploring yet another avenue of vba coding that i am not familiar with.
I have a template word document that i am wanting to fill with items (be it cell values or charts) from an excel file.
I am looking at the coding examples on the net and i am slowly getting there. However i seem to be unable to copy a chart from my excel file and copy into word. I need to be able to specify a specific position on the word document too.
View 9 Replies
View Related
Nov 4, 2009
Has anyone seen this problem before;
I used a "MID" formula to extract some data and for some reason the chart I'm trying to use won't recognize the data. Here is a copy of what the data looks like. Non of the vaules will show up on the chart.
DPJE2DD2CE2CJan1.2784.8984.8997.65Feb1.2697.6597.6591.79B10==MID(B$3,8,5)Mar1.3091.7991.79154.7Apr1.28154.7154.7147.6May1.29147.6147.687.78Jun1.4387.7887.78112.6Jul1.39112.6112.6115.2Aug1.42115.2115.293.6 Sep1.3393.6 93.6 138.9Oct1.28138.9138.9NovDec115.6YTD1.32115.6115.6
View 9 Replies
View Related
Jan 12, 2010
How do I get my charts to update everytime, the data refreshens...I have my series linked to several worksheets within a workbook.
View 9 Replies
View Related
Nov 13, 2008
i am trying to add 50 new data series and it takes hell of a lot of time to do it manually.
Sub Macro6()
Dim total As Integer
Dim Taper As Integer
Taper = 2
Do Until total < 100
total = total + 1
Taper = Taper + 1
ActiveSheet.ChartObjects(" Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection("Total").XValues = "=Data!R" & Taper & "C6"
ActiveChart.SeriesCollection("Total").Values = "=Data!R" & Taper & "C8"
ActiveChart.SeriesCollection("Total"). Name = "=Data!R" & Taper & "C9"
ActiveChart.SeriesCollection("Total").BubbleSizes = "=Data!R" & Taper & "C7"
Loop
End Sub
View 4 Replies
View Related
Sep 29, 2004
I have lots of charts in a sheet "plots" which is based on data in sheet "data Summary". Sheet "plots" have the following code, which is based on the event "actiavte". At the end my main macro, I am activating sheet "plots" and all the charts are working fine. After this if I go to any other sheet in the workbook and come back to "plots", all the charts are flickering and the cursor turns to busy mode. I don't understnad why this is happening.
Private Sub Worksheet_Activate()
For Each ChtOb In ActiveSheet.ChartObjects
ChtOb.Chart.PlotVisibleOnly = False
Next
ThisWorkbook.Worksheets("plots"). Range("e1").Activate
On Error GoTo Handle
With ActiveSheet.ChartObjects("s21max").Chart.Axes(xlCategory, xlSecondary)
.MinimumScale = Application.Average([s21high]) + -4 * Application.StDev([s21high])
.MaximumScale = Application.Average([s21high]) + 4 * Application.StDev([s21high]) + Application.StDev([s21high]) * 0.25
.CrossesAt = .MinimumScale..........................
View 2 Replies
View Related
Jun 9, 2005
I need to export serveral charts to several slides within PowerPoint from Excel Using VBA. I use 2002 versions. I added chartobjects (2) under 1 but not sure if that is the correct placement to start adding more charts to slides.
Private Sub CommandButton1_Click()
'Sub PowerPointOLEAutomation()
Dim ppt As Object, pres As Object
'Create a Microsoft PowerPoint session
Set ppt = CreateObject("powerpoint.application")
'Copy the chart on the Chart Labels Demo sheet
Worksheets("sw dr").ChartObjects(1).Copy
'Worksheets("sw dr&ot").ChartObjects(2).Copy
'Create a new document in Microsoft PowerPoint
Set pres = ppt.Presentations.Add.........................
View 4 Replies
View Related
Jul 18, 2006
Excel Chart can fit various trendlines in a given set of data. For example we can get linear, as well as exponential trend line for same data set.
How do I decide which trendline best represents the trend in data? Shoud R Sq be used as parameter to see the best fit?
View 9 Replies
View Related
Feb 19, 2007
I have a chart which has Autoshapes linked to other cells in the spreadsheet above each bar on the chart. Is there any way of making them dynamic, so that if the value of the bar changes, the Autoshape will move up or down with that bar?
View 4 Replies
View Related
