Solved

need macro to create an x-y graph for worksheet

Posted on 2014-07-31
12
691 Views
Last Modified: 2014-08-07
Hi,

I have attached my excel spreadsheet.   I need to add a function to my existing macro.  Upon pressing a button on the "compare plans" worksheet, the current macro creates a new worksheet, populates it by copying the contents (as values, not formula) of "compare plans", and titles it according to the contents of cell F14.   The new functionality that I need is as follows:  the macro needs to create an x-y line chart that graphs the date (x axis) and 'running balance' (column F), and places the chart in that newly created worksheet (see the current macro).  In other words, when I press the button in "compare charts" worksheet, the contents of "compare charts" are copied to a new worksheet (my existing macro already does this), the new worksheet is titled according to the contents of cell F14 (my existing macro already does this), and a chart showing "running balance" vs time is also displayed in the new worksheet (need added functionality added to my macro).   Note that the active cursor should end up in the original "compare plans" worksheet, rather than change over to the new worksheet.)  The chart title should be obtained from cell F14.  The y axis should be labeled "$" and show values rounded to the nearest $1000, and the x-axis should show the month and year in mm-yy format.

Thanks in advance!
CopySheetToRecord.xlsm
0
Comment
Question by:Cam Raben
  • 8
  • 3
12 Comments
 

Author Comment

by:Cam Raben
ID: 40234446
Just to clarify, I already have a macro (see attached spreadsheet) that does everything except create the x-y line chart.  This question is to add functionality to my macro that will do that.  Thanks.
0
 
LVL 10

Accepted Solution

by:
FamousMortimer earned 350 total points
ID: 40234520
Hi,

How is this?  You can adjust the SizeChart function if necessary to change the size and position.

Option Explicit

Sub CopySheetToRecord()
    Dim ws As Worksheet, shName As String, i As Integer
    
    shName = WorksheetFunction.Text(Sheets("COMPARE PLANS").Range("F14"), "#,###")
    i = 0
    For Each ws In Worksheets
        If ws.Name = shName Then
            i = 1
        End If
    Next ws
    If i = 1 Then
        MsgBox "Sheet " + shName + " exists, copy not performed, program stop", vbCritical
        End
    Else
        Sheets("COMPARE PLANS").Copy After:=Sheets(Worksheets.Count)
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select
        ActiveSheet.Name = shName
        MakeChart (shName)
    End If
End Sub

Sub MakeChart(ByVal shName As String)
    With Sheets(shName)
        .Shapes.AddChart2(227, xlLine).Select
        ActiveChart.SetSourceData Source:=.Range("D22:H" & .Range("E1048576").End(xlUp).Row)
        SizeChart (.Shapes("Chart 1"))
    End With
End Sub

Sub SizeChart(ByVal chart As Shape)
    With chart
        .Top = 82
        .Left = 612
        .Height = 210
        .Width = 520
    End With
End Sub

Open in new window

CopySheetToRecord.xlsm
0
 

Author Comment

by:Cam Raben
ID: 40234627
A couple of points.  

1.  I got an error when I tried to use the macro.  Run time error 438:  object doesn't support this property or method.
Error occured in the shapes.addchart2 line below.

Sub MakeChart(ByVal shName As String)
    With Sheets(shName)
        .Shapes.AddChart2(227, xlLine).Select
        ActiveChart.SetSourceData Source:=.Range("D22:H" & .Range("E1048576").End(xlUp).Row)
        SizeChart (.Shapes("Chart 1"))
    End With
End Sub

2.  The chart that you were able to create and place on the 85,500 tab has some numbers on the x axis that are unknown to me.  Can those be removed?  Also Can we show two x-axis labels per year (Jan 1 and June 1, for each year)?  Or will that be two crowded?

3.  Can we put the cursor focus back on the original tab ("compare plans") rather than leave it on the most recently created worksheet?

Thanks in advance!
0
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 

Author Comment

by:Cam Raben
ID: 40235330
I will be back in the office on Tuesday - thanks.
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 150 total points
ID: 40236000
just change the AddChart2 to AddChart.

but i also added bits to format the axes and corrected the data range.
Sub CopySheetToRecord()
    Dim ws As Worksheet, shName As String, i As Integer
    
    Dim wsStart As Worksheet
    Set wsStart = ActiveSheet
    
    shName = WorksheetFunction.Text(Sheets("COMPARE PLANS").Range("F14"), "#,###")
    i = 0
    For Each ws In Worksheets
        If ws.Name = shName Then
            i = 1
        End If
    Next ws
    If i = 1 Then
        MsgBox "Sheet " + shName + " exists, copy not performed, program stop", vbCritical
        End
    Else
        Sheets("COMPARE PLANS").Copy After:=Sheets(Worksheets.Count)
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select
        ActiveSheet.Name = shName
        MakeChart ActiveSheet.Name
    End If
    
    wsStart.Activate   '<<< reset to start
End Sub

Sub MakeChart(ByVal shName As String)
    Dim c1 As chart
    
    With Sheets(shName)
        .Shapes.AddChart(227, xlLine).Select
        Set c1 = ActiveChart
        
        '<<< desired data range is only columns E & F ??
        c1.SetSourceData Source:=.Range("E22:F" & .Range("E1048576").End(xlUp).Row)
        c1.ChartTitle.Text = .Range("F14").Value
        'c1.Name = .Range("F14").Value
        c1.Axes(xlValue).TickLabels.NumberFormat = "$#,##0"
        c1.Axes(xlValue).DisplayUnit = xlThousands
        c1.Axes(xlCategory).TickLabels.NumberFormat = "mm-yy"  '<<<  mmm-yy may be better
        c1.Legend.Left = 220
        c1.Legend.Top = 21
        c1.PlotArea.Width = 280
        SizeChart .Shapes("Chart 1")

    End With
End Sub

Open in new window

0
 

Author Comment

by:Cam Raben
ID: 40241452
@Robr - Thanks for post.  I tried to run this to no avail.   Could you enable your macro in a working spreadsheet and upload it intact?   Thanks in advance!
0
 

Author Comment

by:Cam Raben
ID: 40243786
The macro did not work Robr.   I am uploading the spreadsheet again, hoping that you can embed your working version of the macro into it and save it and send it back.  Thanks.
CopySheetToRecord.xlsm
0
 

Author Comment

by:Cam Raben
ID: 40244496
By the way, the run-time error 438 states "Object doesn't support this property or method."

And when I debug it, I get an error on the .shapes.addchart2 line below.

Sub MakeChart(ByVal shName As String)
    With Sheets(shName)
        Shapes.AddChart2(227, xlLine).Select
        ActiveChart.SetSourceData Source:=.Range("D22:H" & .Range("E1048576").End(xlUp).Row)
        SizeChart (.Shapes("Chart 1"))
    End With
End Sub
0
 

Author Closing Comment

by:Cam Raben
ID: 40244568
Thanks to you both.  I got it to work, except for Robr's code with his improvements.  I still gave it an A because it's a huge improvement over what I started with - I now have a chart in my spreadsheet.  I appreciate the assistance.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40245283
you have an extra 2 in code, a carry over from Mortimer...

    With Sheets(shName)
        .Shapes.AddChart(227, xlLine).Select

Open in new window



NOT           .Shapes.AddChart2(227, xlLine).Select
0
 

Author Comment

by:Cam Raben
ID: 40245991
Thanks Robr.  That works!  Is there an easy way to also graph the IRA value on the same chart?  (column H of 'compare plans' tab?)    Also, if I want to set my cursor back into the 'compare plans' tab, do i have to write a new subroutine or is there a line of VBA code that I could put at the end of my routine that would do this?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40247987
my code version of CopySheetToRecord also had this...

   
    Dim wsStart As Worksheet
    Set wsStart = ActiveSheet  '<<<< save current sheet
....
.....

wsStart.Activate   '<<< reset to start    

have added the modified workbook as requested
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question