Scribd
Upload
10 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
10 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

Sub CreateChartWithSlicers()

On Error Resume Next

'Delete existing sheet


[Link] = False
On Error Resume Next
Sheets("Chart").Delete
Set wsChart = [Link]
[Link] = "Chart"
[Link] = True

'Set data source


Set wsData = Workbooks("[Link]").Sheets("SDR-DATA")
sourceRange = [Link] & "!R4C1:R" & _
[Link]([Link], 1).End(xlUp).Row & "C" & _
[Link](4, [Link]).End(xlToLeft).Column

'Create pivot table for chart


Set pvtTable = Workbooks("[Link]").[Link]( _
SourceType:=xlDatabase, SourceData:=sourceRange).CreatePivotTable( _
TableDestination:=[Link]("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 [Link]
Case "AVAILABLE", "SHORTAGE"
[Link] = True
Case Else
[Link] = 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 = [Link]( _
Left:=[Link]("A3").Left, _
Top:=[Link]("A3").Top, _
Width:=900, Height:=400)

With [Link]
.SetSourceData Source:=pvtTable.TableRange2
.ChartType = xlBarClustered

'Remove gridlines and axes


.Axes(xlCategory).HasMajorGridlines = False
.Axes(xlValue).HasMajorGridlines = False
.Axes(xlCategory).[Link] = 10
.Axes(xlValue).[Link] = 0

'Add data labels


With .SeriesCollection(1)
.HasDataLabels = True
.[Link] = True
.[Link] = xlOutsideEnd
.[Link] = RGB(146, 208, 80) 'Green for Available
End With

With .SeriesCollection(2)
.HasDataLabels = True
.[Link] = True
.[Link] = xlOutsideEnd
.[Link] = RGB(255, 0, 0) 'Red for Shortage
End With

'Remove legend
.HasLegend = False
End With

'Create Buyer Slicer


Set slcCache = [Link].Add2( _
pvtTable, "Buyer", "ChartBuyerSlicer")

With [Link]( _
wsChart, , "Buyers", "Select Buyers", 10, 10, 200, 300)
.Style = "SlicerStyleLight1"
.Caption = "Select Buyers"
End With

'Cleanup
[Link]
[Link] = True
[Link]("A1").Select

If [Link] <> 0 Then MsgBox "Error: " & [Link]


End Sub

You might also like