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
Hi P,
Welcome to VBAX.
Is this what you're after?
Regards
MD
[vba]
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
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
How about a different approach, one chart that changes to reflect the currently selected row?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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?
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
The one you did works best as it eliminates the appearance of 0%.Originally Posted by mdmackillop
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!!
Give this a try
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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?
Posting Permissions
Reply With Quote
