Change Chart Max Axis
Sub ChartMaxAxis()
Dim ct As Chart
Dim AxisMaxValue As Integer
Dim sh1 As String
Dim sh2 As String
Dim sh3 As String
sh1 = "Slide 7 (1)" '< change sheet name here
sh2 = "Slide 7 (2)" '< change sheet name here
sh3 = "Slide 7 (3)" '< change sheet name here
AxisMaxValue = 2000 '< change to any cell
'sheet Slide 7 (1)
Set ct = Sheets(sh1).ChartObjects("Chart 2").Chart
With ct
.Axes(xlValue).MaximumScale = AxisMaxValue
End With
Set ct = Nothing
'sheet Slide 7 (2)
Set ct = Sheets(sh2).ChartObjects("Chart 2").Chart
With ct
.Axes(xlValue).MaximumScale = AxisMaxValue
End With
Set ct = Nothing
'sheet Slide 7 (3)
Set ct = Sheets(sh3).ChartObjects("Chart 2").Chart
With ct
.Axes(xlValue).MaximumScale = AxisMaxValue
End With
Set ct = Nothing
End Sub
Create a Chart from Data
Sub CreateChart()
Dim ws As Worksheet
Dim chartObj As ChartObject
Set ws = ThisWorkbook.Sheets("Sheet1")
' Create a new chart
Set chartObj = ws.ChartObjects.Add(Left:=100, Top:=100, Width:=400, Height:=300)
' Set data range
chartObj.Chart.SetSourceData Source:=ws.Range("A1:B10")
' Set chart type
chartObj.Chart.ChartType = xlColumnClustered
End Sub
Change Chart Type
Sub ChangeChartType()
ActiveChart.ChartType = xlLine
End Sub
Add Chart Title
Sub AddChartTitle()
With ActiveChart
.HasTitle = True
.ChartTitle.text = "Monthly Sales Performance"
End With
End Sub
Add Axis Titles
Sub AddAxisTitles()
With ActiveChart
.HasTitle = True
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.text = "Revenue ($)"
End With
End Sub
Change Chart Colors
Sub ChangeChartColors()
Dim s As Series
For Each s In ActiveChart.SeriesCollection
s.Format.Fill.ForeColor.RGB = RGB(0, 128, 255)
Next s
End Sub
Add Data Labels
Sub AddDataLabels()
Dim s As Series
For Each s In ActiveChart.SeriesCollection
s.HasDataLabels = True
Next s
End Sub
Create a Pie Chart
Sub CreatePieChart()
Dim ws As Worksheet
Dim chartObj As ChartObject
Set ws = ActiveSheet
Set chartObj = ws.ChartObjects.Add(100, 100, 400, 300)
chartObj.Chart.SetSourceData Source:=ws.Range("A1:B5")
chartObj.Chart.ChartType = xlPie
End Sub
Move Chart to a New Sheet
Sub MoveChartToSheet()
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Sales Chart"
End Sub
Update Chart Data Dynamically
Sub UpdateChartData()
Dim ws As Worksheet
Dim ch As ChartObject
Set ws = Sheet1
Set ch = ws.ChartObjects("Chart 1")
ch.Chart.SetSourceData Source:=ws.Range("A1:B20")
End Sub
Loop Through All Charts and Refresh
Sub RefreshAllCharts()
Dim ws As Worksheet
Dim ch As ChartObject
For Each ws In ThisWorkbook.Sheets
For Each ch In ws.ChartObjects
ch.Chart.Refresh
Next ch
Next ws
End Sub
Add a Secondary Axis
Sub AddSecondaryAxis()
With ActiveChart.SeriesCollection.NewSeries
.Name = "Profit"
.Values = Range("C2:C10")
.AxisGroup = xlSecondary
End With
End Sub
Export Chart as an Image
Sub ExportChartAsImage()
Dim ch As Chart
Set ch = ActiveChart
ch.Export Filename:="C:\Users\Public\Documents\ChartImage.png", FilterName:="PNG"
End Sub
Resize and Move Chart
Sub ResizeAndMoveChart()
Dim ch As ChartObject
Set ch = ActiveSheet.ChartObjects("Chart 1")
ch.Left = 50
ch.Top = 50
ch.Width = 500
ch.Height = 350
End Sub
Change Chart Style
Sub ChangeChartStyle()
ActiveChart.ChartStyle = 4 ' Choose from 1–48
End Sub
Add Trendline
Sub AddTrendline()
ActiveChart.SeriesCollection(1).Trendlines.Add Type:=xlLinear
End Sub
Add Chart Legend
Sub AddChartLegend()
With ActiveChart
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
End With
End Sub
Create a Line Chart Automatically
Sub CreateLineChart()
Dim ws As Worksheet
Dim ch As ChartObject
Set ws = ActiveSheet
Set ch = ws.ChartObjects.Add(100, 100, 400, 300)
ch.Chart.SetSourceData Source:=ws.Range("A1:B12")
ch.Chart.ChartType = xlLineMarkers
ch.Chart.HasTitle = True
ch.Chart.ChartTitle.text = "Yearly Sales Growth"
End Sub
Delete All Charts
Sub DeleteAllCharts()
Dim ws As Worksheet
Dim ch As ChartObject
For Each ws In ThisWorkbook.Sheets
For Each ch In ws.ChartObjects
ch.Delete
Next ch
Next ws
End Sub
Copy Chart to Another Sheet
Sub CopyChartToAnotherSheet()
Sheets("Sheet1").ChartObjects("Chart 1").Copy
Sheets("Sheet2").Paste
End Sub
Automatically Create Charts for Each Column
Sub CreateChartsForEachColumn()
Dim ws As Worksheet
Dim i As Integer, chartObj As ChartObject
Set ws = ActiveSheet
For i = 2 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set chartObj = ws.ChartObjects.Add(Left:=100 * i, Top:=50, Width:=300, Height:=200)
chartObj.Chart.SetSourceData Source:=ws.Range(ws.Cells(1, 1), ws.Cells(10, i))
chartObj.Chart.ChartType = xlColumnClustered
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.text = ws.Cells(1, i).Value
Next i
End Sub