asked on

need macro to create an x-y graph for worksheet

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

Cam Raben

ASKER

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.

ASKER CERTIFIED SOLUTION

FamousMortimer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cam Raben

ASKER

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!

Cam Raben

ASKER

I will be back in the office on Tuesday - thanks.

SOLUTION

Robberbaron (robr)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cam Raben

ASKER

@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!

Cam Raben

ASKER

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

Cam Raben

ASKER

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

Cam Raben

ASKER

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.

Robberbaron (robr)

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

Cam Raben

ASKER

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?

Robberbaron (robr)

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