Producing multiple charts

**shep2010** · 10-22-2011, 09:36 AM

Hi,

I'm not sure if you can help, i'd appreciate it it a lot if you could. I need a chart for multiple merchants to track suspicious sales. e.g i would chart certian sales for a month (30 or 31 days) and look for spikes.

i need to produce around 50 charts a day to identify these spikes and evidence tht we have monitored a certian merchants.

The format is the first column has merchant names and then each row contains represents each days figures going through the merchant. in the charts i'd like to have the date represented in the axis e.g 01/10/2011 etc. As I need to look at and document each of these and to produce multiple tables is very time consuming. i'd appreciate any help with this.

attached is an example of the data/format. Any help would be appreciated.

All the best,

Steve

**mrice** · 10-22-2011, 10:16 AM

Please have a look at the attachment. The code is in the module 1 tab in the VBA editor (Alt F11).

**shep2010** · 10-22-2011, 11:27 AM

Hi,

Thank you so much for replying, i really appreciate it. This doesn't seem to work on my data though. i did download it and i tried to run the macro but it said unable to compile in the programming area. i haven't really used macros as such and i'm guessing my computer isn't liking something. Sorry to be a pain, i'd appreciate your help as you seem to be pretty amazing at this stuff, from what i've seen.

All the best,

Stephen

**shep2010** · 10-22-2011, 11:38 AM

If it helps i get compile error. Argument not optional:

"SetSourceData"

Cheers,

Steve

**mrice** · 10-22-2011, 01:49 PM

OK - try this amended version. I forgot to take out some code that I had recorded.

**shep2010** · 10-22-2011, 02:52 PM

Hey,

Thank you so much. This is great! it will save me so much time! i really appreciate and I owe you one!
I know i'm probably pushin my luck, and it isn't vital at all. I just wondered if i can make the charts big enough so that each day is shown on the Horizontal axis? it isn't major and what you've done is amazing! now i can get one with the analytical work. Thank you so much. i owe you a drink!

Steve

**mrice** · 10-22-2011, 03:18 PM

No problem. Replace the macro code with this.

Sub Test()
Dim DateRange As Range
Sheets("Example").Activate
Application.ScreenUpdating = False
Set DateRange = Range(Cells(1, 2), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Application.StatusBar = "Create chart for " & Cells(N, 1)
    ChartName = Cells(N, 1).Value
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range(Cells(N, 2), Cells(N, Cells(1, Columns.Count).End(xlToLeft).Column))
    ActiveChart.ChartType = xlXYScatterLines
    ActiveChart.Legend.Delete
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=ChartName
    Sheets(ChartName).Move After:=Sheets(Sheets.Count)
    ActiveChart.SeriesCollection(1).XValues = "='Example'!" & DateRange.Address
    ActiveWindow.Zoom = 70
    ActiveChart.PlotArea.Height = 375
    ActiveChart.Axes(xlCategory).MajorUnit = 1
    ActiveChart.Axes(xlCategory).TickLabels.Orientation = 45
    Sheets("Example").Activate
Next N
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

**shep2010** · 10-22-2011, 06:27 PM

Hi,

Thank you. you really didn't have to do that. it didn't work as it comes up saying run time error 1004 and that "method 'location' of object '_chart' failed" but don't feel you need to to sort it as i've taken up enough of your time.

Thank you,

Steve

**mrice** · 10-23-2011, 11:42 AM

Interesting, it seems to work OK for me.

If there is a problem, I would be keen to sort it out as others may look at this thread in the future.

When you get the run time error, can you hit Debug and tell me which line is highlighted in yellow?

**shep2010** · 10-24-2011, 02:54 PM

Hey,

I was being stupid. I forgot to delete all the original code. Sorry. This works amazingly. it has reduced the amount of time i waste producing charts. Now this may be a bit much to ask and i might post it in a new thread, but i found out i need to do the weekly charts too. I thought i could just add them to the same macro you made and the dates wouldn't be an issue, however it looks as though even though i have a week ending date the charts like to have 30 days. is ther anyway i canedit the macro you sent so it can do these weekly charts? if not no worries. you've solved my original query amazingly and i'm really greatful. if you or anyone else wants to look at my current sheet i need to sort you are more than welcome.

Thank yo uso much

**mrice** · 10-24-2011, 04:17 PM

You just need to change one line if I understand correctly.

ActiveChart.Axes(xlCategory).MajorUnit = 7

**shep2010** · 10-24-2011, 05:58 PM

Thanks for having a look. i get an error meesage after changing editing it. it says runtime error 1004 method location of object '_chart ' failed. not sure if it's becasue there are only 6/7 column titles now for each week. Thanks for trying though.

Steve

**mrice** · 10-25-2011, 02:44 AM

Working example attached.

Thread: Producing multiple charts

Thread Tools

Display

Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Re: Producing multiple charts

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions