Dear Joel..

I just tested the code and it seems to be what I needed but I have a query.
The result which is being shown by the Pie chart is not clear. I mean.. I am
not able to find if it shows the work done or the work remaining..

Can you please explain this as well

Thanks a lot in advance,
--
Best Regards,
Jitendra Kumar


"Jitendra Kumar" wrote:

Dear Joel,

I will try this code and will let you know the results..

Thanks a lot,
--
Best Regards,
Jitendra Kumar


"Joel" wrote:

try this code

Sub MakeChart()

'clear sheet 2
Sheets("Sheet2").Cells.Clear
With Sheets("Sheet1")
'get last row on sheet 1
Sh1LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'copy uniaque cell in Sheet 1 - Col A to Sheet2 - Col A
.Range("A2:A" & Sh1LastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A2"), _
Unique:=True
End With

With Sheets("Sheet2")
'Create header sheet 2
.Range("A1") = "Category"
.Range("B1") = "Percentage"
'get last row in sheet 2, unique items
Sh2LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'create a formula for cell B2 on sheet 2
PercentageFormula = _
"=sumproduct(--(sheet1!$A$2:$A$" & Sh1LastRow _
& "=A2),--(sheet1!$C$2:$C$" & Sh1LastRow & "=""Done""))/" & _
"countif(sheet1!$A:$A,A2)"
'put formula in cell B2
.Range("B2").Formula = PercentageFormula
'copy formula in B2 down column B
.Range("B2").Copy Destination:=.Range("B3:B" & Sh2LastRow)
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("A1:B" &
Sh2LastRow), _
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet
With ActiveChart
.SeriesCollection(1).ApplyDataLabels ShowPercentage:=True
.SeriesCollection(1).DataLabels.Position = xlLabelPositionCenter
.HasTitle = True
.ChartTitle.Characters.Text = "Percentage"
End With
End With

End Sub


"Jitendra Kumar" wrote:

Dear Joel,

Thanks a lot for the help and your interest in solving my problem.

See there are various categories in the Sheet like "Networking",
"Electrical", "Infrastructure" and each category has various tasks like
"Cabling", "Conduting", etc. The user can update a task with its related
category as many times he wants untill the task is finished. if the task is
not finished then the task status field will be blank and if a task is
finished then it will be updated in the "Task Status" field as "Done". I want
a code which can extract a unique list of categories and the number of task
in each category. Now if there are 10 Tasks defined in a Category and there
are 3 Tasks with status "Done" then a pie chart should be shown on a userform
with the figures something similar as mentioned below:

Networking - 25%
Electrical - 20%
Infrastructure - 0%

The PIe Chart should be based on a dynamic range so that if the data
increases then the range of pie chart also increaes in its next execution.

Thanks a lot in advance for your kidn help...

--
Best Regards,
Jitendra Kumar


"Joel" wrote:

I read your request earlier, but I need additional info to get it correct.

1) Do you need a seperate chart for each category? Your request asked for
"the macro should first extract the unique list of tasks " I think you meant
category.

2) You asked for a form, is it ok to move chart to a new sheet?


I would use filters and get a unique list of categories by using an advance
Filter and putting results in sheet2.

Sub MakeChart()

'clear sheet 2
Sheets("Sheet2").Cells.Clear
With Sheets("Sheet1")
'get last row on sheet 1
Sh1LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'copy uniaque cell in Sheet 1 - Col A to Sheet2 - Col A
.Range("A2:A" & Sh1LastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A2"), _
Unique:=True
End With

With Sheets("Sheet2")
'copy header row on sheet 1 to sheet 2
Sheets("Sheet1").Rows(1).Copy Destination:=.Rows(1)
'get last row in sheet 2, unique items
Sh2LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'create a formula for cell B2 on sheet 2
PercentageFormula = _
"=sumproduct(--(sheet1!$A$2:$A$" & Sh1LastRow _
& "=A2),--(sheet1!$C$2:$C$" & Sh1LastRow & "=""Done""))/" & _
"countif(sheet1!$A:$A,A2)"
'put formula in cell B2
.Range("B2").Formula = PercentageFormula
'copy formula in B2 down column B
.Range("B2").Copy Destination:=Range("B3:B" & Sh2LastRow)
End With
End Sub

If this is what you want then I can add the chart automation or you can
Record a macro with the options you need and I can make the need
modifications to make it dynamic.

"Jitendra Kumar" wrote:

Dear Merjet,

I want to create a dynamic pie chart based on the entries done in the sheet,
place it somewhere in a sheet, export it to GIF and then import it on a
Userform as Image. I have the code for placing it on a userform but I dont
know how to dynamicaly create pie charts. please help.

--
Best Regards,
Jitendra Kumar


"merjet" wrote:

This doesn't seem like a task for a pie chart (the parts comprise the
whole). A bar chart with two bars seems more apt, but you'd have to
summarize the data before creating it. You can put charts on a
UserForm using the Microsoft Office Chart control. If it's not already
in your controls Toolbox, right-click the Toolbox and then check it.

Hth,
Merjet