Macro to create a chart

[keith2511]

I need to create lots of pie charts for a dashboard and was hoping to record a macro that I could run once I have selected the data.

However the macro fails here:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+z
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Range("'Trend Table'!$M$3:$M$4")
ActiveChart.ApplyChartTemplate ( _
"C:\Users\k\AppData\Roaming\Microsoft\Templates\Charts\Mini-pie.crtx" _
)
ActiveSheet.Shapes("Chart 7").Height = 93.5433070866
ActiveSheet.Shapes("Chart 7").Width = 96.3779527559
End Sub

I guess it's because Chart 7 already exists? Also the area I used to record the macro is hard coded in, even though I chose to use relative addresses.

Grateful for advice on how to get around this.

Thanks in advance.

 

[Worf]

Hi

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer$, nSize&) As Long


Sub Macro1()
Dim lpBuff As String * 25, ret&, UName$, ch As Shape
ret = GetUserName(lpBuff, 25)
Set ch = ActiveSheet.Shapes.AddChart
ch.Chart.ChartType = xlPie
ch.Chart.SetSourceData Source:=Range("'Trend Table'!" & Selection.Address)  ' selected cells
ch.Chart.ApplyChartTemplate ("C:\Users\" & Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) & _
"\AppData\Roaming\Microsoft\Templates\Charts\minipie.crtx")
ch.Height = 94
ch.Width = 96
End Sub