Scribd
Upload
17 views2 pages

Create Chart with Slicers in Excel

This VBA script creates a new chart sheet in Excel by deleting any existing sheet named 'Chart'. It sets up a pivot table based on data from a specified workbook, configures the pivot table fields, and generates a clustered bar chart with specific formatting and data labels. Additionally, it adds a slicer for filtering by buyers and includes error handling throughout the process.

Uploaded by

Izhar Mehdi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
17 views2 pages

Create Chart with Slicers in Excel

This VBA script creates a new chart sheet in Excel by deleting any existing sheet named 'Chart'. It sets up a pivot table based on data from a specified workbook, configures the pivot table fields, and generates a clustered bar chart with specific formatting and data labels. Additionally, it adds a slicer for filtering by buyers and includes error handling throughout the process.

Uploaded by

Izhar Mehdi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

Sub CreateChartWithSlicers()

On Error Resume Next

'Delete existing sheet


Application.DisplayAlerts = False
On Error Resume Next
Sheets("Chart").Delete
Set wsChart = Sheets.Add
wsChart.Name = "Chart"
Application.DisplayAlerts = True

'Set data source


Set wsData = Workbooks("SDR-2025.xlsx").Sheets("SDR-DATA")
sourceRange = wsData.Name & "!R4C1:R" & _
wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row & "C" & _
wsData.Cells(4, wsData.Columns.Count).End(xlToLeft).Column

'Create pivot table for chart


Set pvtTable = Workbooks("SDR-2025.xlsx").PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=sourceRange).CreatePivotTable( _
TableDestination:=wsChart.Range("A3"), TableName:="ChartPivot")

'Configure pivot table


With pvtTable
'Set fields
With .PivotFields("Wk No")
.Orientation = xlColumnField
.Position = 1
End With

With .PivotFields("PO Note")


.Orientation = xlRowField
.Position = 1
'Show only AVAILABLE and SHORTAGE
For Each pi In .PivotItems
Select Case pi.Name
Case "AVAILABLE", "SHORTAGE"
pi.Visible = True
Case Else
pi.Visible = False
End Select
Next pi
End With

'Add count
.AddDataField .PivotFields("Assembly/ Part"), "Count of Parts", xlCount

'Remove subtotals and grand totals


.ColumnGrand = False
.RowGrand = False
End With

'Create chart
Set pvtChart = wsChart.ChartObjects.Add( _
Left:=wsChart.Range("A3").Left, _
Top:=wsChart.Range("A3").Top, _
Width:=900, Height:=400)

With pvtChart.Chart
.SetSourceData Source:=pvtTable.TableRange2
.ChartType = xlBarClustered

'Remove gridlines and axes


.Axes(xlCategory).HasMajorGridlines = False
.Axes(xlValue).HasMajorGridlines = False
.Axes(xlCategory).TickLabels.Font.Size = 10
.Axes(xlValue).TickLabels.Font.Size = 0

'Add data labels


With .SeriesCollection(1)
.HasDataLabels = True
.DataLabels.ShowValue = True
.DataLabels.Position = xlOutsideEnd
.Format.Fill.ForeColor.RGB = RGB(146, 208, 80) 'Green for Available
End With

With .SeriesCollection(2)
.HasDataLabels = True
.DataLabels.ShowValue = True
.DataLabels.Position = xlOutsideEnd
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0) 'Red for Shortage
End With

'Remove legend
.HasLegend = False
End With

'Create Buyer Slicer


Set slcCache = ActiveWorkbook.SlicerCaches.Add2( _
pvtTable, "Buyer", "ChartBuyerSlicer")

With slcCache.Slicers.Add( _
wsChart, , "Buyers", "Select Buyers", 10, 10, 200, 300)
.Style = "SlicerStyleLight1"
.Caption = "Select Buyers"
End With

'Cleanup
wsChart.Activate
pvtTable.ShowTableStyleRowStripes = True
wsChart.Range("A1").Select

If Err.Number <> 0 Then MsgBox "Error: " & Err.Description


End Sub

You might also like