Macros for making charts

Lokicue

New Member
Joined
Mar 19, 2002
Messages
4
I need to make a macros that populates 124 separate charts with different data. On sheet one, the data is there with six columns and three rows. The charts have to be bar charts and they are reading different rows each time. Please help.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
My experience on this board tells me that you will find it dificult to get someone to solve this problem in it's entirety. I suggest that you be prepared for a reasonably slow process. We'll help you to learn how to do it, but finding someone with the time to do the whole thing is tricky.

Here's some questions to help you put more detail into your request:

Can you do any of this yourself? i.e. What is your VBA experience? Do you record macros and use them or do you frequently create code from scratch? Or none of the above?

Do you know how to create a chart in VBA?

Will the charts be embedded in a sheet or will they be separate chartsheets?

Is all of the data going to be on one sheet or do you have several sheets with a separate page for each data set?

What ranges are used to store the data and why is the range different each time?

That's all I can think of right now. I hope it's a good start.
 
Upvote 0
I usually record the macros. I can do some of it myself, but yes, I'm very new to this. The charts have separate chart sheets and all of the charts are reading from the same sheet. The reason why the range is different each time is because the data has the same units, but different questions that each chart has to represent. I actually have a spreadsheet with an example, but I can't post it on here. Thanks.
 
Upvote 0
On 2002-03-20 11:56, Lokicue wrote:
The reason why the range is different each time is because the data has the same units, but different questions that each chart has to represent. I actually have a spreadsheet with an example, but I can't post it on here. Thanks.

OK, what you're asking is simple enough, except for the bit about the data. I'm not quite following you. If the info is not confidential, email a copy of the workbook without macros to me at j_b68@hotmail.com

Also, create a chart in the workbook that is representative of the format you want.

An explanation of the data and exactly what you would probably be good as well. (Since I'll be looking at the data with the explanation.)

If I can help, I'll post the VBA code here, so that others can see it.

Cheers
 
Upvote 0
OK, this turned into an hellacious mini-project, the like of which I'll never do again, but it was educational so I've got no hard feelings.

I'm using XL2000 on a 800MHz PC running NT4 SP6 (I think).

The workbook I was given contained a data sheet and a sample chart that was embedded on a separate sheet.

My intention was to copy the sample chart and simply change the data series each time to create a new chart.

This worked great until I got to chart 70. I was then getting "1004" errors. A quick look in the MSKB produced this little gem:

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q210684

What this meant was that I had to move my code into a different workbook so that I could save and close the data workbook after a certain number of charts had been created. (this is the only workaround MS offered to get rid of the "1004" error.

The code then worked great, until I hit chart 100 and got an "Automation Error". It turned out I'd hit the fonts limit as described by this joyous article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168650

So I got rid of Autoscale and made all the fonts on the chart the same. (it's strange that I got this error since I'm using XL2000 and this is apparently an XL97 only bug)

Finally after many opening and closings of Excel I got the code to create 124 charts on 124 worksheets. Writing this explanation has now taken me into the 3 hour mark for solving the problem. Yes I am an idiot, yes I should get fired, but this looked like work.

Anyway, here is the code that I finally came up with, I'm posting it here for posterity:

<pre>
Option Explicit

Public Sub CreateCharts()

'Declare Variables
Dim oBook As Workbook
Dim sFullPath As String
Dim DataSht As Worksheet
Dim TemplateSht As Worksheet
Dim sht As Worksheet
Dim cht As Chart
Dim sQuestion As String
Dim sNameFirstPart As String
Dim sNameSecondPart As String
Dim sSeries1Values As String
Dim sSeries2Values As String
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim iFirstCol As Integer
Dim iLastCol As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim iCount As Integer

'Turn off screen updates to speed up code
Application.ScreenUpdating = False

' Initialise Variables

sFullPath = "C:TempCrazySpreadsheet.xls" 'This is where we will save our workbook.
Set oBook = Workbooks("CrazySpreadsheet.xls") 'Set the workbook to the data workbook.
Set DataSht = oBook.Sheets("Inpt.Data") 'The sheet with the data
Set TemplateSht = oBook.Sheets("TEMPLATE") 'The template sheet we're copying
iFirstCol = 2 'First column with data
iLastCol = 8 'Last column with data
iFirstRow = 4 'First row with data
iLastRow = DataSht.Range("A65536").End(xlUp).Row 'Last row with data. This is picked up automatically

For iCol = iFirstCol To iLastCol
sNameFirstPart = DataSht.Cells(iFirstRow - 2, iCol).Value 'First part of the sheet name, i.e. the Unit name

For iRow = iFirstRow To iLastRow Step 4
sQuestion = DataSht.Cells((iRow - 1), 2).Value 'The question for the next sheet
sNameSecondPart = "(" & Left(sQuestion, (InStr(1, sQuestion, ":") - 1)) & ")" 'Lastpart of the sheetname i.e. the question number
'Copy Template
TemplateSht.Copy After:=oBook.Sheets.Item(oBook.Sheets.Count)
Set sht = Sheets(Sheets.Count)
Set cht = sht.ChartObjects(1).Chart

'Increment counter
iCount = iCount + 1

'Rename sheet
sht.Name = sNameFirstPart & sNameSecondPart

'Add unit name and question to the sheet with chart
sht.Range("C3").Value = sNameFirstPart
sht.Range("B5").Value = sht.Range("B5").Value & " " & sQuestion

'Set new data ranges
sSeries1Values = "=(Inpt.Data!R" & iRow & "C" & iCol & ",Inpt.Data!R" & iRow & "C9:R" & iRow & "C10)"
sSeries2Values = "=(Inpt.Data!R" & (iRow + 1) & "C" & iCol & ",Inpt.Data!R" & (iRow + 1) & "C9:R" & (iRow + 1) & "C10)"

'Change old data ranges
With cht
.SeriesCollection(1).Values = sSeries1Values
.SeriesCollection(2).Values = sSeries2Values
End With
'Save and close book because of Excel limitation with copying sheets
If iCount Mod 20 = 0 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open(sFullPath)
Set DataSht = oBook.Sheets("Inpt.Data") 'The sheet with the data
Set TemplateSht = oBook.Sheets("TEMPLATE") 'The template sheet we're copying
End If


Next
Next

End Sub</pre>

That's all I have to say about that.

Thank you.

Good night.

(Disclaimer, this is probably not the best way to solve this problem, if I had more time I'd have created the charts programmatically.)
 
Upvote 0
No problem, have you got it working? One thing I forgot to tell you was that I changed your data spread sheet slightly. the first two questions, questions 15A and questions 1A (I think) were in a different format to the rest of the questions on the sheet. These two cells had "Question: 15A etc etc." in one cell whereas all of the other cells had "Question" in one cell and "15A - etc etc" in another. So I broke 15A and 1A into separate cells. The reason I did this was so that I could rename the sheets using the "15A" and "1A" parts more easily and to make the sheet uniform.

I hope this makes sense.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top