Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Repetitive graphing task for multiple worksheets (Excel 2000)

    I want to create a multisheet excel file with all the necessary data (I'll call this the "data sheet") in one worksheet. This data will be used to plot several graphs - I want all the graphs to be on their own separate sheet. Each graph will utilize a separate column of data from the "data sheet" and will be the graph specification but will use different columns of data.

    It seems like there should be a way to automate this task - but I can't figure out how. I want each graph to reference the "data sheet" but want each consecutive graph to keep moving over one consecutive column to "grab" the data.

    Can anyone point me in the right direction?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Record a macro to do the first chart and post it as a reply and I'll rewrite it and put it in a loop for you. Need the macro from you so that I know what type of chart that you want. Is column A the data x-axis labels for each chart? How do you want to name the chart sheets? What about the chart title? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    OK, Colleen's asleep, so I'll make up my own specs (always easier <img src=/S/grin.gif border=0 alt=grin width=15 height=15> )as documented in the macro below. To aviod confusion, I'm also attaching my test workbook. If this is not enough info, just make another post. HTH --Sam
    <pre>Option Explicit
    Sub PlotColumns()
    ' Assumptions:
    ' Column 1 has the X-Values
    ' Columns 2 - n have the Y-Values
    ' A separate chart sheet will be created for each Y-column
    ' The first row contains the chart name
    ' The second row contains a title for each chart sheet
    ' The third row contains text for the axis titles
    ' The numerical data to be plotted starts in row 4
    ' No limit (except sheet limits) to the number of points or
    ' number of charts
    ' Charts are xy-scatter plots with connecting lines
    ' Charts are all scaled to the overall min & max
    '
    Dim mySheet As Worksheet ' Save my place
    Dim rngText As Range ' All the titles and axis descriptions
    Dim rngPlot As Range ' All the data to plot
    Dim rngX As Range, rngY As Range
    Dim minX As Long, minY As Long
    Dim maxX As Long, maxY As Long
    Dim i As Integer
    Application.ScreenUpdating = False
    Set mySheet = ActiveSheet
    With ActiveSheet.UsedRange
    Set rngText = .Rows("1:3")
    Set rngX = .Range(.Cells(4, 1), _
    .Cells(.Rows.Count, 1))
    Set rngPlot = .Rows("4:" & .Rows.Count)
    ' Initially rngY is all of the Y's
    Set rngY = .Range(.Cells(4, 2), _
    .Cells(.Rows.Count, .Columns.Count))
    End With
    ' Compute the overall min's & max's
    minX = Int(Application.Min(rngX))
    maxX = Int(Application.Max(rngX)) + 1
    minY = Int(Application.Min(rngY))
    maxY = Int(Application.Max(rngY)) + 1
    For i = 2 To rngPlot.Columns.Count ' Plot each column
    Set rngY = rngPlot.Columns(i)
    With Charts.Add
    .Name = rngText.Cells(1, i) ' Sheet name
    .ChartType = xlXYScatterLines
    .SetSourceData Union(rngX, rngY)
    .HasLegend = False ' With just one series, no legend
    .HasTitle = True
    .ChartTitle.Text = rngText.Cells(2, i).Text
    With .Axes(xlCategory, xlPrimary)
    .HasMajorGridlines = True
    .HasTitle = True
    .AxisTitle.Text = rngText.Cells(3, 1).Text
    .MinimumScale = minX
    .MaximumScale = maxX
    End With
    With .Axes(xlValue, xlPrimary)
    .HasMajorGridlines = True
    .HasTitle = True
    .AxisTitle.Text = rngText.Cells(3, i).Text
    .MinimumScale = minY
    .MaximumScale = maxY
    End With
    .Move after:=Sheets(Sheets.Count)
    .Deselect
    End With
    Next i
    ' Cleanup
    mySheet.Activate
    Set rngText = Nothing
    Set rngX = Nothing
    Set rngPlot = Nothing
    Set rngY = Nothing
    Application.ScreenUpdating = True
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    SammyB:
    You are a genius!! I was not sleeping - just busy and was not prepared to write the initial macro that you requested. I have the file you created and will be studying it to try and figure out what you did and to apply it to my situation, but it looks like you fully grasped what I was trying to do! THANK YOU, so much. I am a REAL beginner when it comes to macros. I am a research person who works with SPSS a lot and I use excel for relatively simple things, but I would like to learn more.

    I also went to Brainbench.com - it looks very interesting. Is that where you work or is it your company?

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Not a genius, just been plotting data forever <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>. In the "good old days," we actually replaced the printer type belt with a "moon" chain -- lots of different size dots, so you could get different gray levels.

    I work for MTL Systems as a subcontractor for Georgia Tech Research Institute, teach at Clark State Community College, review some of the Brainbench tests, answer questions here, and home educate a teenager. So far, only MTL and CSCC pay me! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    So, did you decipher my code? Some of it may be hard to understand. Just ask if you have trouble. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Maybe my Autochrt.zip from:

    http://www.bmsltd.ie/mvp

    is useful here.

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Sam,
    Came across your post and wondered if you had used this method on charts with more than one series?
    If you have any examples I would appreciate it.
    Thanks,
    Scott

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    You probably need to give me more details of what you want. In my example, each column represents a different series. I can help you with the SeriesCollection and the Series objects. I normally use them to add each series, but the SetSourceData method will create all of the series at once. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Sam,
    Thanks for responding to this old post.
    I attached an example of what I'm wanting to do. There will be one datasheet with all the models.
    I want to create the different model graphs on their own sheet.
    I know the numbers in the spreadsheet and the chart don't match. I pasted the chart from another sheet to let you see the output that I want.
    If you think this can be done let me know.
    Thanks,
    Scott

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Scott, what a nice challenge! I have attached a copy of your worksheet with a solution. The code is below. I made several assumptions that you may not like; for example, even though you can programmically enter the categories, it is a lot simpler to have them as the first row on the plot range. Also, I only do one plot; we can easily make a loop to do all of them. The plot does not look exactly like your picture: it was closer last night, but I was using XL2003 and I noticed today that you have XL2K, so I've made a couple of changes and tested it on a XL2K machine. We can easily fix anything you don't like.

    Just a few comments on the code. I basically recorded a macro to do the plot and them got rid of the selection object and explicit range references. A couple of other observations: (1) initially I plotted everything as a line plot, then I had a single SeriesCollection. But, when I changed the MIF ChartType to columns, excel created chart groups, so after that I used code such as ".ChartGroups(2).SeriesCollection(3).." (2) Excel had the line ".Location Where:=xlLocationAsObject..." early on, but I moved it to the very end. When that line is executed, the initial Chart disappears and it becomes a ChartObject, so the with block no longer has an object. HTH --Sam
    <pre>Option Explicit

    Sub plotModel()
    Dim r As Range, rDefault As Range
    Dim rData As Range
    Dim s As String, iFirst As Long
    On Error GoTo Bail ' Exit on cancel button
    Set rDefault = Cells(ActiveCell.Row, 1)
    Set r = Application.InputBox( _
    Prompt:="Select first cell of model to plot", _
    Title:="Model", Default:=rDefault.Address, _
    Type:=8)
    On Error GoTo 0
    s = r.Text
    iFirst = r.Row
    InsertDateRows iFirst
    Set rData = Range(Cells(iFirst + 1, 2), Cells(iFirst + 5, 14))
    makeChart rData, s
    Bail:
    End Sub

    Sub InsertDateRows(iFirst As Long)
    Rows(iFirst & ":" & iFirst + 1).Insert Shift:=xlDown
    Rows(iFirst & ":" & iFirst + 1).Interior.ColorIndex = xlNone
    With Cells(iFirst, 3)
    .FormulaR1C1 = "=R[" & iFirst & "]C15-(COLUMN(R[" & iFirst & "]C15)-COLUMN(RC)-1)*32"
    .NumberFormat = "mmm yyyy"
    .AutoFill Destination:=Range(.Offset(0, 0), .Offset(0, 11)), Type:=xlFillDefault
    End With
    With Cells(iFirst + 1, 3)
    .FormulaR1C1 = "=LEFT(TEXT(R[-1]C,""mmm""),1)&TEXT(R[-1]C,""yy"")"
    .AutoFill Destination:=Range(.Offset(0, 0), .Offset(0, 11)), Type:=xlFillDefault
    End With
    End Sub

    Sub makeChart(rData As Range, sTitle As String)
    With Charts.Add
    .ChartType = xlLineMarkers
    .SetSourceData Source:=rData, PlotBy:=xlRows
    .HasTitle = True
    .ChartTitle.Characters.Text = sTitle
    .Axes(xlCategory).HasMajorGridlines = False
    .Axes(xlValue).HasMajorGridlines = False
    .HasLegend = True
    .Legend.Position = xlTop
    .Legend.Border.LineStyle = xlNone
    .SeriesCollection(1).AxisGroup = 2 ' MIL
    .SeriesCollection(1).ChartType = xlColumnClustered
    .Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "#,##0"
    .Axes(xlValue, xlSecondary).MinimumScale = 0
    .Axes(xlValue, xlSecondary).MaximumScale = _
    .Axes(xlValue, xlSecondary).MaximumScale * 2
    .SeriesCollection(1).Interior.ColorIndex = 34 ' Turquoise
    With .SeriesCollection(4) ' Visit rate
    .Border.ColorIndex = 55 ' Indigo
    .MarkerBackgroundColorIndex = 55
    .MarkerForegroundColorIndex = 55
    .MarkerStyle = xlDiamond
    End With
    .ChartGroups(2).SeriesCollection(3).PlotOrder = 2 ' Visit Rate
    .Location Where:=xlLocationAsObject, Name:=rData.Parent.Name
    End With
    With ActiveSheet.ChartObjects(ActiveSheet.ChartObjects. Count)
    .Width = .Width * 1.2
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Sam,
    HTH is an understatement!
    Wow, <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    I certainly didn't expect all this work. I was just looking to see if the scenerio could be done.
    I don't have time today to look deeply into the code but I will on Monday. At first test it does exactly what I need.
    I will want to loop through all the models and I'll see if I can figure that out.
    I know more Access syntax that Excel so I'm glad people like you are out there.
    Thanks for all your hard work. Let me know where to send the 12 Pack.
    I'll let you know if I have any questions after looking at it in more depth.
    Many Thanks again,
    Scott

  12. #12
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Sam,
    The code works OK in 2000 and 2003.
    I'm trying to figure out the looping. I get an error after the first loop. Could you take a look at it.
    I commented the lines I added and where it errors.

    Thanks,
    Scott

  13. #13
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Sam,
    Thats correct, I want the chart for each model to be on its own sheet with the sheet name being the model number.
    Actually the data sheet should end up being the last sheet. 1st model sheet1, 2nd model sheet2 ...etc...Data sheet
    If its not hard to hide the x-data rows that would be desireable.
    Thanks for your help,
    Scott

  14. #14
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    I'm still trying to get all the graphs to be on their own separate sheet with the Model as the sheet name, move the sheet to
    the end, and then loop to the next model on the worksheet and do the same.
    I modified your code to loop but I'm stuck at this line of code after 1 loop:
    Set rData = Range(Cells(iFirst + 1, 2), Cells(iFirst + 5, 14))
    After the first loop I get the message "Method 'Cells' of object'_Global' failed"
    If you click on the command button you will see what I men.
    Any help would be welcomed.
    Scott

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Repetitive graphing task for multiple worksheets (Excel 2000)

    Try this.

    <pre>With mySheet
    Set rData = .Range(.Cells(iFirst + 1, 2), .Cells(iFirst + 5, 14))
    End With</pre>


    I don't think it likes doing it when the selection is a chart since it has no cell objects. This now explicitly works with the sheet not the chart (implicit, since it is the selection)

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •