Option Explicit
Sub CreateMonthlyCharts()
Dim rng As Range
Dim cvrRng As Range
Dim cht As ChartObject
Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set ws = Worksheets("Field Report")
Set ws2 = Worksheets("Monthly Report")
Set ws3 = Worksheets("Monthly Data Transfer")
'Copies everything from the Field Report sheet to the Monthly report sheet
ws.Range("A:BO").Copy Destination:=ws2.Range("J:BX")
'this piece of code deletes the shape buttons copied from the field report sheet
' it will ignore the PE Logo on the sheet
Dim Shp As Shape
For Each Shp In ws.Shapes
If Not (Shp.Type = msoPicture) Then Shp.Delete
Next Shp
'Start point for building up the charts
Set rng = ws3.Range("f1:f31,AU1:AU31") 'Selects Flow data range
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170) 'Create chart on Monthly Report
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("A40:j60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width ' resize
cht.Top = cvrRng.Top ' reposition
cht.Left = cvrRng.Left ' reposition
'Building the chart for Flows
With cht.Chart
.HasTitle = True
.ChartTitle.Text = "Flows, m³"
.ChartTitle.Font.Size = 10
.SetSourceData Source:=rng
.ChartType = xlLine
'.SetElement (msoElementPrimaryValueAxisShow)
'.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
.SetElement (msoElementLegendTop)
'legend
.Legend.Font.Size = 8
'y-axis name
'.Axes(xlValue, xlPrimary).HasTitle = True
'.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
'Color the lines
'note that series 1 = Raw Water, series 2 = treated water
.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
.SeriesCollection(1).Format.Line.Weight = 1.5 'line width
.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 176, 80) 'Green
.SeriesCollection(2).Format.Line.Weight = 1.5
'Select last point of data and add a marker+data label
With .SeriesCollection(1).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
.DataLabel.Position = xlLabelPositionBelow
End With
With .SeriesCollection(2).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
End With
'Start point for building up the charts
Set rng = ws3.Range("C1:D31,AR1:AR31,AT1:AT31") 'Selects Raw/Treated pH/Temp data range
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("K40:T60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width ' resize
cht.Top = cvrRng.Top ' reposition
cht.Left = cvrRng.Left ' reposition
'Building the chart for Flows
With cht.Chart
.HasTitle = True
.ChartTitle.Text = "Raw/Treated pH/Temp°C"
.ChartTitle.Font.Size = 10
.SetSourceData Source:=rng
.ChartType = xlLine
'.SetElement (msoElementPrimaryValueAxisShow)
'.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
.SetElement (msoElementLegendTop)
'legend
.Legend.Font.Size = 8
'y-axis name
'.Axes(xlValue, xlPrimary).HasTitle = True
'.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
'Color the lines
.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
.SeriesCollection(1).Format.Line.Weight = 1.5 'line width
.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 176, 240) 'light blue
.SeriesCollection(2).Format.Line.Weight = 1.5
.SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(0, 176, 80) 'Green
.SeriesCollection(3).Format.Line.Weight = 1.5
.SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(146, 208, 80) 'light Green
.SeriesCollection(4).Format.Line.Weight = 1.5
'Sets ph/temp series into secondary axis
.SeriesCollection(1).AxisGroup = 2
.SeriesCollection(3).AxisGroup = 2
'Select last point of data and add a marker+data label
With .SeriesCollection(1).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
.DataLabel.Position = xlLabelPositionBelow
End With
With .SeriesCollection(2).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
With .SeriesCollection(3).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
With .SeriesCollection(4).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
End With
'Start point for building up the charts
Set rng = ws3.Range("E1:E31,Q1:R31,BJ1:BL31") 'Selects all the turbidity data
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("U40:AD60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width ' resize
cht.Top = cvrRng.Top ' reposition
cht.Left = cvrRng.Left ' reposition
'Building the chart for Flows
With cht.Chart
.HasTitle = True
.ChartTitle.Text = "Turbidity, nTU"
.ChartTitle.Font.Size = 10
.SetSourceData Source:=rng
.ChartType = xlLine
'.SetElement (msoElementPrimaryValueAxisShow)
'.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
.SetElement (msoElementLegendTop)
'legend
.Legend.Font.Size = 8
'y-axis name
'.Axes(xlValue, xlPrimary).HasTitle = True
'.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
'Color the lines
.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
.SeriesCollection(1).Format.Line.Weight = 1.5 'line width
.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 192, 80) 'Orange
.SeriesCollection(2).Format.Line.Weight = 1.5
.SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(0, 176, 80) 'Green
.SeriesCollection(3).Format.Line.Weight = 1.5
.SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(112, 48, 160) 'purple
.SeriesCollection(4).Format.Line.Weight = 1.5
'Select last point of data and add a marker+data label
With .SeriesCollection(1).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
.DataLabel.Position = xlLabelPositionBelow
End With
With .SeriesCollection(2).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
With .SeriesCollection(3).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
With .SeriesCollection(4).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
End With
'Start point for building up the charts
Set rng = ws3.Range("AJ1:AJ31,AM1:AN31,AQ1:AQ31,BJ1:BJ31") 'Selects chlorine data ranges
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("AE40:AN60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width ' resize
cht.Top = cvrRng.Top ' reposition
cht.Left = cvrRng.Left ' reposition
'Building the chart for Flows
With cht.Chart
.HasTitle = True
.ChartTitle.Text = "Chlorine, mg/L"
.ChartTitle.Font.Size = 10
.SetSourceData Source:=rng
.ChartType = xlLine
'.SetElement (msoElementPrimaryValueAxisShow)
'.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
.SetElement (msoElementLegendTop)
'legend
.Legend.Font.Size = 8
'y-axis name
'.Axes(xlValue, xlPrimary).HasTitle = True
'.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
'Color the lines
.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
.SeriesCollection(1).Format.Line.Weight = 1.5 'line width
.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'Red
.SeriesCollection(2).Format.Line.Weight = 1.5
.SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(255, 192, 80) 'Orange
.SeriesCollection(3).Format.Line.Weight = 1.5
.SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(255, 255, 0) 'Yellow
.SeriesCollection(4).Format.Line.Weight = 1.5
.SeriesCollection(5).Format.Line.ForeColor.RGB = RGB(112, 48, 160) 'Purple
.SeriesCollection(5).Format.Line.Weight = 1.5
'Select last point of data and add a marker+data label
With .SeriesCollection(1).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
.DataLabel.Position = xlLabelPositionBelow
End With
With .SeriesCollection(2).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
With .SeriesCollection(3).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
With .SeriesCollection(4).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
With .SeriesCollection(5).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
End With
'Start point for building up the charts
Set rng = ws3.Range("BF1:BG31") 'Selects CT Actual & Perf. Ratio data range
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("AO40:AX60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width ' resize
cht.Top = cvrRng.Top ' reposition
cht.Left = cvrRng.Left ' reposition
'Building the chart for Flows
With cht.Chart
.HasTitle = True
.ChartTitle.Text = "CT Actual/CT Perf. Ratio"
.ChartTitle.Font.Size = 10
.SetSourceData Source:=rng
.ChartType = xlLine
'.SetElement (msoElementPrimaryValueAxisShow)
'.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
.SetElement (msoElementLegendTop)
'legend
.Legend.Font.Size = 8
'y-axis name
'.Axes(xlValue, xlPrimary).HasTitle = True
'.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
'Color the lines
'note that series 1 = Raw Water, series 2 = treated water
.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
.SeriesCollection(1).Format.Line.Weight = 1.5 'line width
.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 176, 80) 'Green
.SeriesCollection(2).Format.Line.Weight = 1.5
'Select last point of data and add a marker+data label
With .SeriesCollection(1).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
.DataLabel.Position = xlLabelPositionBelow
End With
With .SeriesCollection(2).Points(30)
.HasDataLabel = True
.MarkerStyle = 2
.Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
.DataLabel.Position = xlLabelPositionAbove
End With
End With
End Sub