VBA Macro Code To Create Multiple Charts
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
ADVERTISEMENT
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
Mar 6, 2008
I'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.
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
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
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
Oct 23, 2008
I need a macro to create the following worksheets and charts from an Excel data set:
Three (3) worksheets (already created manually in attached Excel file):
1. Chart Data.
(a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr).
(b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset.
2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data).
3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data)
At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
View 14 Replies
View Related
Dec 22, 2006
i want to create dynamic Excel Charts in one sheet ,The sheeet contain dynamic columns and rows and i want to create for each 4 columns a chart in a new sheet ,i started to try it in VBA and didnt founs the way to select 4 columns as a range, i succeseed to do it with msgboox that the user enter the range but i want to do it in a loop
View 9 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
Nov 1, 2013
I am trying to create a Macro to pull from a list and then update the charts and print. I have a list that has over 100 clients. I believe I need to loop but I am not familiar with VBAs at all. Below is what I am trying to accomplish.
Sub Update()
'
' Update Macro
'
'
Selection.Copy
ActiveSheet.Next.Select
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate
[Code] ......
View 3 Replies
View Related
Apr 13, 2008
Consider two instances of Excel - one with a workbook containing a chart and some VBA code, the other containing a blank workbook. When I paste a chart from the first instance of Excel to the other, all the VBA code from the original workbook is also pasted into a new third hidden workbook in the second instance of Excel! If that weren't weird enough ON WINDOWS XP (but not Windows 2000) if the VBA code included WorkbookOpen or AutoOpen this code is executed in the second instance of Excel, which of course fails if it references sheets that only existed in the workbook open in the first instance of Excel! This is the first time I've seen the same version of Excel do different things on different versions of Windows! Example attached - What's going on!? Auto Merged Post Until 24 Hrs Passes;...note: this does not happen when pasting charts between workbooks open in the SAME instance of Excel
View 6 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
Aug 10, 2014
Refer to attached file.
I have below code which successfully create a macro button and assign the macro correctly.
This is only doing for one sheet and i need to modify the code so that it does for all sheets of the workbook.
[Code] ....
Test Macro_Botton.xlsm
View 3 Replies
View Related
Feb 22, 2008
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!)
The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code...
The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work).
The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments.
'this code calls the method which creates the buttons in specific cells with specific names
Private Sub CommandButton3_Click()
'the first call always works!
AddCmdbuttonWithCode "Edit_111_111_114", 23
'the second one crashes excel
AddCmdbuttonWithCode "Edit_111_111_115", 27
End Sub
View 3 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 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
Sep 1, 2006
I have been trying to create a macro that creates a new workbook and prompts the user to enter a new filename for it. I'm getting stuck with the code.
View 3 Replies
View Related
Jan 11, 2007
Right now I use a VBA-Code to create new worksheets in a workbook. my Question:
is it possible to attach code to the newly created Worksheet via vba-code??
View 3 Replies
View Related
Nov 13, 2009
how to create dashboards or charts. I am looking for similar website like http://www.datapigtechnologies.com/. this is very good website for those of you who need to do some fancy staff with excel.
View 2 Replies
View Related
Feb 23, 2014
Basically I want to have a macro to loop through and make all the pie charts for each location but only for the top 10 of each location.
I also want the legend to be more precise for example, for London the first one in the legend should show:
Column C + "-" + Column I + "-" + Column J
Which would be: London - Rol9 -13
Instead of it being: London data rol9 13
Attached is the document in question where I've made the graphs manually : Example_Pies.xlsx
View 3 Replies
View Related
Jul 6, 2008
I have an excel file which contains following data in it.
Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200
I need to create following three workbooks with name based on Cust Cd from above excel file.
Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.
Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.
Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.
View 4 Replies
View Related
May 2, 2014
I have a macro code that will create line graph referring the data given in defined column A1-C4,
Code with Static column range:-
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet2'!$A$1:$C$4")
ActiveChart.ChartType = xlLineStacked
I tried to modify the above code, so it will refer undefined/dynamic data column, but getting an error during execution "Run Time Error - 424:" "Object required"
Code with Dynamic column range:-
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("a1", _
ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
ActiveChart.ChartType = xlLineStacked
View 1 Replies
View Related
Apr 20, 2008
This code create a userform on the workbook opening .I'm having problems with
1. Closeing the Visual basic window after this procedure runs
2. Being able to use the create userformstext box value in other procedures
3. how to delete the userform on close
Sub AddUserFormInputRequest()
Dim objVBProj As VBProject
Dim objVBComp As VBComponent
Dim objVBFrm As UserForm
Dim objChkBox As Object
Dim x As Integer
Dim strCode As String
Dim firstLine As Long, SecondLine As Long
Set objVBProj = Application.VBE.ActiveVBProject
Set objVBComp = objVBProj.VBComponents.Add(vbext_ct_MSForm)
With objVBComp
. Name = "InputRequest"
. Properties("Width") = 200
.Properties("Height") = 100......................
View 6 Replies
View Related
Jan 29, 2013
Create an individual pie chart for each row of data (roughly 2000 rows in master list). Data to be charted contains 2 cells (Benefit and Salary). Once the chart is created I need to save the chart as an image (assuming it will need to go into Paint or another program?) Once the images are created and saved to a folder I can add the image link (file location) to a merge document data source. This way I will be able pull in the pie chart (now a picture) with a merge field during a mail merge.
Sample Data
Employee
Benefit
Salary
[Code]....
View 2 Replies
View Related
Apr 7, 2013
I normally create charts and use the same formatting for my colours i.e. Gradient fill with Shadow etc...
Now each time i create my charts i am having to keep do the formatting again and again and if i create a new spreadsheet with charts i have to do it all over again
Is there any way i can save this formatting colour so i can quickly apply to my chart series etc?
View 2 Replies
View Related
Jul 10, 2009
I'd like to put a spinner button on a chart that will change the x-axis date range. The data is in day/month/year format, but ideally I'd like to have the spinner button move in 1 year increments. The most recent date should always be present and changes should just impact how far back in history to go. I have basically no experience with vba coding, so please be as thorough and simple with your responses/solutions.
View 2 Replies
View Related
Jun 10, 2008
i am having a problem of updating my graphs in the application i devloped, my application is like this, go to sheet 1 click showuserform button and suer form contains combo and list box select the sheetname in the combo and select two values in the list and click plot.it plots the graphs acording to the selected values but my problem is it makes a newchart every time when we select a new week range it is getting stacked...it is not getting updated with the most recently plotted graph ??? i am facing this problem when i am opening my application in other computer...u can see my problem when u open my application. i am attaching my workbook and this is the code i used for making the chart
Sub MakeChart()
Dim rng1 As Range, rng2 As Range
Dim gs As String
gs = ComboBox1.Value
Application. ScreenUpdating = True
'First selected
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Set rng1 = Columns(1). Find(ListBox1.List(i))
Exit For
End If
Next
'Last selected
For i = ListBox1.ListCount - 1 To 1 Step -1..............................
View 4 Replies
View Related
Jul 12, 2012
I have a quite big database as a result of a study in Excel 2010. There is a special block of data for every participant in the study, including 16 data series in 16 lines. I have to make a unique chart for every line.
One block is bulid up in a way, that every row is the input for a chart, with the exception of one "spacer" row, which is empty.
Name
1 2 3 4 ...
A
B
C
D
E
F
For example in the sample above, there should be 6 line-charts (A, B, C, D, E, F) next to the block.
Can a macro be written, so I can automatically create the chart-set by selecting the block's upper left cell and running the macro? Or is there any easier method?
View 5 Replies
View Related
Aug 2, 2013
I am trying to write a macro that goes in an excel file that creates a powerpoint presentation, and puts four (4) charts in each slide.. I currently have a code that is pasting all of the charts in the same slide and I can't figure out why it isn't working (side note: I haven't attempted to resize or relocate the pictures on the powerpoint slides yet)..
Code:
'Add a reference to the Microsoft PowerPoint Library by:
'1. Go to Tools in the VBA menu
'2. Click on Reference
'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay
'keep button in same location
Set btn = ActiveSheet.Shapes("CommandButton17")
With btn
btLeft = .Left
btTop = .Top
End With
[code]....
View 2 Replies
View Related
Aug 12, 2008
I have an excel sheet populated with loads of values. There is a space between each set of data. I need to draw a graph for each section and i cant work out how to do it. I have attached the sheet in question. The gap between each column has "space" written on row, which is needed to prevent it being deleted. The drawing of the graphs needs to be automated as there is going to be 100's of sheets containg lots of data. The column size of each section can change so it needs to be dynamic. I have attached an example sheet
View 5 Replies
View Related
