Macros for making charts

[Lokicue]

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.

[Mark O'Brien]

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.

[Lokicue]

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.

[Mark O'Brien]

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

[Lokicue]

K. I sent it to you. Thank you so much for taking a look at it.

[Mark O'Brien]

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/...;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...;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:

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

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

[Lokicue]

Thank you so much Sorry about it taking so long.

[Mark O'Brien]

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.