I have two tasks that I need some help with relating to creating and moving charts.
1. I have created a chart which gets its source data from range "A3:C26". I now want to create 12 additional charts which get their data from across 4 columns each. So the next chart will use range "D3:G26", the next "H3:K26", the next "L3:O26"...and so on, with the last from "AV3:AY26". Can you please help me with the code to put this in a For Loop or some other method.
2. I then want to move these 13 charts from wsMC worksheet and place them one under each other on a worksheet called POTOC-Charts.
Can you please help me with the two tasks.
Here is what I have done so far:
Private Sub OSMCharts_Click()
Dim wsMC As Worksheet, wsDoc As Worksheet
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
Dim Chrtname As String, lookfor As String, myMonthYear As String
Dim nbrMonth As Integer, i As Integer
Set wsDoc = ThisWorkbook.Worksheets("Daily OSM Checklist")
Set wsMC = ThisWorkbook.Worksheets("Monthly Charts")
wsMC.Activate
'initialize variables
nbrMonth = Month(wsDoc.Range("B3"))
myMonthYear = MonthName(nbrMonth, True) & " - " & Year(wsDoc.Range("B3"))
Chrtname = wsMC.Range("A2") & " for " & myMonthYear
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = wsMC.Range("A3:C26")
' define chart's X values
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=40, Width:=900, Top:=350, Height:=225)
With myChtObj.Chart
' make an XY chart
.ChartType = xlColumnClustered
'chart name
.HasTitle = True
.ChartTitle.Text = Chrtname
.ChartTitle.Font.Size = 12
.ChartTitle.Font.Color = vbBlack
'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hour"
.ChartArea.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
With wsMC.ChartObjects(1).Chart.PlotArea.Format.Fill
.Visible = msoFalse
.Visible = msoTrue
.TwoColorGradient msoGradientHorizontal, 1
.ForeColor.RGB = RGB(255, 255, 0)
.BackColor.RGB = RGB(0, 176, 240)
End With
.HasDataTable = True
.DataTable.HasBorderOutline = True
.HasLegend = False
' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
' add series from selected range, column by column
For iColumn = 2 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next
With .SeriesCollection(1)
.Name = "Online Availability"
.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
End With
With .SeriesCollection(2)
.ChartType = xlLine
.MarkerStyle = xlMarkerStyleDiamond
.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
End With
End With
End Sub
1. I have created a chart which gets its source data from range "A3:C26". I now want to create 12 additional charts which get their data from across 4 columns each. So the next chart will use range "D3:G26", the next "H3:K26", the next "L3:O26"...and so on, with the last from "AV3:AY26". Can you please help me with the code to put this in a For Loop or some other method.
2. I then want to move these 13 charts from wsMC worksheet and place them one under each other on a worksheet called POTOC-Charts.
Can you please help me with the two tasks.
Here is what I have done so far:
Private Sub OSMCharts_Click()
Dim wsMC As Worksheet, wsDoc As Worksheet
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
Dim Chrtname As String, lookfor As String, myMonthYear As String
Dim nbrMonth As Integer, i As Integer
Set wsDoc = ThisWorkbook.Worksheets("Daily OSM Checklist")
Set wsMC = ThisWorkbook.Worksheets("Monthly Charts")
wsMC.Activate
'initialize variables
nbrMonth = Month(wsDoc.Range("B3"))
myMonthYear = MonthName(nbrMonth, True) & " - " & Year(wsDoc.Range("B3"))
Chrtname = wsMC.Range("A2") & " for " & myMonthYear
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = wsMC.Range("A3:C26")
' define chart's X values
With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
End With
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=40, Width:=900, Top:=350, Height:=225)
With myChtObj.Chart
' make an XY chart
.ChartType = xlColumnClustered
'chart name
.HasTitle = True
.ChartTitle.Text = Chrtname
.ChartTitle.Font.Size = 12
.ChartTitle.Font.Color = vbBlack
'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Hour"
.ChartArea.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
With wsMC.ChartObjects(1).Chart.PlotArea.Format.Fill
.Visible = msoFalse
.Visible = msoTrue
.TwoColorGradient msoGradientHorizontal, 1
.ForeColor.RGB = RGB(255, 255, 0)
.BackColor.RGB = RGB(0, 176, 240)
End With
.HasDataTable = True
.DataTable.HasBorderOutline = True
.HasLegend = False
' remove extra series
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
' add series from selected range, column by column
For iColumn = 2 To rngChtData.Columns.Count
With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal
.Name = rngChtData(1, iColumn)
End With
Next
With .SeriesCollection(1)
.Name = "Online Availability"
.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
End With
With .SeriesCollection(2)
.ChartType = xlLine
.MarkerStyle = xlMarkerStyleDiamond
.Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
End With
End With
End Sub
