Solved

need macro to create an x-y graph for worksheet

Posted on 2014-07-31
12
644 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now