Creating charts automaticaly in Excel

[PJSR]

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]

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]

How about a different approach, one chart that changes to reflect the currently selected row?

[PJSR]

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]

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]

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]

Give this a try

[PJSR]

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?