PDA

View Full Version : Creating charts automaticaly in Excel



PJSR
07-20-2007, 04:05 AM
Hi,

I need to create +- 90 charts reflecting the same info just new people.

I attached an example. Row 1 is the constant and the rows below the variable data. I need a chart for each of the rows as in the example chart.

My questions:

1) Is there a way to use Excel to do this automatically? Or must I build a chart manually for each of the items?

2) Can one set it up to ignore columns with no value?

Thanks

P

mdmackillop
07-20-2007, 05:38 AM
Hi P,
Welcome to VBAX.
Is this what you're after?
Regards
MD


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Dest As Range, cel As Range
Set Dest = Range("AA2").Resize(1, 12)
Application.ScreenUpdating = False
If Target.Column = 1 And Target.Row > 1 Then
Target.Resize(1, 12).Copy Dest
For Each cel In Dest
If cel = "" Then
cel.ColumnWidth = 0
Else
cel.ColumnWidth = 12
End If
Next
End If
Application.ScreenUpdating = True
End Sub

Bob Phillips
07-20-2007, 05:40 AM
How about a different approach, one chart that changes to reflect the currently selected row?

PJSR
07-20-2007, 05:46 AM
Does the job!!! Just the layout of the chart sometimes creates problems. Labels on each other for example.

Will it not be better for printing purposes to have each chart in it's own worksheet? I mean to then set the printing properties to print one sheet per A4?

mdmackillop
07-20-2007, 05:53 AM
You have two slightly different solutions with regards to chart key. Which one is preferred?
Are you wanting to save the workbook with 90 chart sheets, or do you just wish the facility to print them out.
BTW, if you wish something presented in a particular fashion, please show this in the example.

PJSR
07-20-2007, 06:03 AM
You have two slightly different solutions with regards to chart key. Which one is preferred?
Are you wanting to save the workbook with 90 chart sheets, or do you just wish the facility to print them out.
BTW, if you wish something presented in a particular fashion, please show this in the example.

The one you did works best as it eliminates the appearance of 0%.

I don't nessesarely need the charts to be saved. Need to be able to print them.

BTW thanks for the help it's already saving me a lot of time over the weekend!!

mdmackillop
07-20-2007, 09:04 AM
Give this a try

PJSR
07-21-2007, 03:19 AM
Give this a try

Does the trick!! Thanks.

Now just one more question: Would it be possible to edit the charts before print as some of the data labels arent spaced corectly?