Pie Chart with List box - Named Ranges

     

    Steps to follow:

     

  • Create Named Ranges

    •  

  • Update Listbox with Names

    •  

  • Select required name from list box to create chart

    •  

     

    Sub Create_Charts_Monthwise()

    'Create Names to the Data
    Dim sh2 As Worksheet
    Set sh2 = ThisWorkbook.Sheets("sheet2")

    fr = sh2.Range("A1").End(xlToRight).Column
    last = sh2.Range("A1").End(xlDown).Row

    For i = 1 To fr
    Range(Cells(1, i), Cells(last, i)).Select
    ThisWorkbook.Names.Add Name:=Cells(1, i).Value, RefersTo:=Selection
    If i > 1 Then
    UserForm1.ListBox1.AddItem Cells(1, i).Value
    End If
    Next
    'Assinging Names to Listbox

    UserForm1.Show
    End Sub

    Download The Workbook

    Private Sub CommandButton1_Click()

    Dim sh2 As Worksheet
    Set sh2 = ThisWorkbook.Sheets("Sheet2")
    Dim i As Integer

    For i = 0 To ListBox1.ListCount

    If ListBox1.Selected(i) = True Then
    s = Me.ListBox1.List(i)
    Dim sh As Worksheet
    Set sh = Worksheets.Add(after:=Worksheets(Sheets.Count))
    sh2.Activate
    Union(Range(s), Range("Item")).Copy sh.Range("A1")
    sh.Activate
    ActiveWindow.DisplayGridlines = False
    ActiveSheet.Name = s & " Sales Report"

    Dim ch As ChartObject
    With sh.Range("D2:L19")
    Set ch = sh.ChartObjects.Add( _
    Left:=.Left, _
    Height:=.Height, _
    Width:=.Width, _
    Top:=.Top)
    End With

    With ch.Chart

    .ChartType = xlPie
    .SeriesCollection.NewSeries
    Range("A1").CurrentRegion.Select
    .SetSourceData Source:=Selection, PlotBy:=xlColumns
    .SeriesCollection(1).ApplyDataLabels
    .Legend.Position = xlLegendPositionRight
    .ChartStyle = 26
    .HasTitle = True
    .ChartTitle.Text = ActiveSheet.Name
    '.DataTable.HasBorderOutline = True
    .ChartArea.Border.ColorIndex = 50
    .ChartArea.Border.Weight = xlThick
    End With
    End If
    Next
    sh.Columns.AutoFit
    End Sub