Graph Macro in Excel 2007

Microsoft Office excel 2007 home & stude...
June 4, 2010 at 07:39:00
Specs: Windows XP
Thanks to Derby and Humar for their help on the macro yesterday. Today, I've come across another problem. Based on the information that is separated into different worksheets based on years, I need to show that the money collected in a graph format. The problem that I need help with is, I need a macro that would graph column E (money collected) of each spreadsheet on the y-axis and then graph the months based on column B (date) of each spreadsheet. It also needs to be able to update itself from information in sheet 1. I know it sounds complicated, but i just can't figure it out. Any help would be appreciated.
The macro that is already being used for this workbook to separate information from sheet 1 to other worksheets based on year is pasted below:

Option Explicit

Private Sub Button1_Click()

Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strYear As String
Dim rngSearch As Range
Dim rngFind As Range

On Error GoTo ErrHnd

'stop screen updating to increase speed
Application.ScreenUpdating = False

With Worksheets("Sheet1")
'set start as row after cell with 'Copied' in it
'if 'Copied' not found use B2 i.e., after heading row in column B
Set rngSearch = Range("B2:B" & CStr(Application.Rows.Count))
Set rngFind = rngSearch.Find("Copied", LookIn:=xlValues)

If rngFind Is Nothing Then
'Copied not found - so start at B2
Set rngStart = .Range("B2")
'Copied found
'set start to row after 'Copied'
Set rngStart = rngFind.Offset(1, 0)
'delete the row containing 'Copied'
End If

'set end - last used row in column B
Set rngEnd = .Range("B" & CStr(Application.Rows.Count)).End(xlUp)

'loop through cells in column B
For Each rngCell In Range(rngStart, rngEnd)
'Extract year to use as Sheet name
strYear = Application.WorksheetFunction.Text(Year(rngCell), "####")
'test if tab exists
On Error Resume Next
If Not Worksheets(strYear).Name <> "" Then
On Error GoTo ErrHnd
'No worksheet of this name - so create one
Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = strYear
'copy header row then and copy row of data
.Range("A1").EntireRow.Copy Destination:=Worksheets(strYear).Range("A1")
rngCell.EntireRow.Copy Destination:=Worksheets(strYear).Range("A2")
On Error GoTo ErrHnd
'worksheet exists
'copy row to end of used range
rngCell.EntireRow.Copy Destination:=Worksheets(strYear).Range("A1") _
.Offset(Worksheets(strYear).UsedRange.Rows.Count, 0)
End If
Next rngCell
'flag end of copied data (in column B)
rngEnd.Offset(1, 0).Value = "Copied"
End With

'restore screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
'restore screen updating
Application.ScreenUpdating = True
End Sub

See More: Graph Macro in Excel 2007

Report •

June 5, 2010 at 13:08:58

I am not sure why you are looking for a macro to create a chart from existing data.

Assuming that you have dates in column B (say B2:B100) and amounts in E2:E100),
then create a table of months.
In cells G1 to R1 enter dates for the first of each month for the year in question.
Format G1 to R1 as months "mmmm"
In cell G2 enter this formula:
Note the $ signs.
Now drag to extend the formula to cell R2
R2 will contain:

Cells G2 to R2 will contain the monthly amounts.

Select cells G1 to R2 and from the menu bar select 'Insert' - 'Chart'
and create a chart of amounts by month.

When completed, you can select the category axis, right-click, select 'Format Axis...' and select the number tab, and change the date display to just months if required, e.g., mmmm


Report •

June 8, 2010 at 07:04:15
Humar, again, I can't express how thankful I am for you helping me out. Hope all is well. Thanks for the help.

Report •

June 9, 2010 at 06:50:28
Your very welcome.

Thanks for letting us know everything worked.



Report •

Related Solutions

Ask Question