This software uses a GPLv3 Software License, if you are unfamiliar with the terms of this license check the attachments to this file.
 
Sub Button1_Click()
'These Variables might not be necessary, you might be able to delete them,
'but delete at your own risk
Dim myValueRowE As String
Dim myValueRowF As String
Dim myValueRowG As String
Dim myValueRowH As String
Dim myValueRowI As String
Dim myValueCol As String


'Refresh the page
ActiveWorkbook.RefreshAll


'Generate the Input Box to enter the Starting Week of the Lex Deliveries
myValueColStart = InputBox("Insert the week and year (ww.yyyy) you want the Data Series to start with (if the week is between 1 and 9 then do not enter a zero before the week number)")
'Generate the Input Box to enter the Ending Week of the Lex Deliveries

myValueColEnd = InputBox("Insert the week and year (ww.yyyy) you want the Data Series to end with (if the week is between 1 and 9 then do not enter a zero before the week number)")


'This pulls the column letter of the column that contains the starting date
Set Cell = Worksheets("Delivery STN by Week").Range("B1:ZZ1").Cells.Find(myValueColStart, , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then
ColLetterStartConsume = Split(Cell.Address, "$")(1)
Else
MsgBox "I cannot find that text on this sheet"
End If
'This pulls the column letter of the column that contains the ending date
Set Cell = Worksheets("Delivery STN by Week").Range("B1:ZZ1").Cells.Find(myValueColEnd, , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then
ColLetterEndConsume = Split(Cell.Address, "$")(1)
Else
MsgBox "I cannot find that text on this sheet"
End If

'These are for getting the ranges of cells (from the Consume Product sheet) to enter into the Ranges/Series into the Lex Deliveries chart

'Tripp, Replace (Lew Woodchip Row) 73 and 73
myValueRowLexWoodchip = "'Delivery STN by week'" + "!$" + ColLetterStartConsume + "$73:$" + ColLetterEndConsume + "$73"

'Tripp, Replace (Lex Sawdust Row) 128 and 128
myValueRowLexSawdust = "'Delivery STN by week'" + "!$" + ColLetterStartConsume + "$128:$" + ColLetterEndConsume + "$128"

'Tripp, Replace (Total Delivery Row) 129 and 129
myValueRowTotalDelivery = "'Delivery STN by week'" + "!$" + ColLetterStartConsume + "$129:$" + ColLetterEndConsume + "$129"

'Tripp, Replace (Consumption Row) 137 and 137
myValueRowConsumption = "'Delivery STN by week'" + "!$" + ColLetterStartConsume + "$137:$" + ColLetterEndConsume + "$137"

'Tripp, Replace (Closing Stock Row) 144 and 144
myValueRowClosingStock = "'Delivery STN by week'" + "!$" + ColLetterStartConsume + "$144:$" + ColLetterEndConsume + "$144"
 
'Generating the X-Axis for the ranges/series for the Lex Deliveries Chart
myValueRowXAxisSecond = "'Delivery STN by week'" + "!$" + ColLetterStartConsume + "$1:$" + ColLetterEndConsume + "$1"
 
'Generate the Values that will be stored as ranges further down in the code
Dim FirstRange As Range
Dim SecondRange As Range
Dim ThirdRange As Range
Dim FourthRange As Range
Dim FifthRange As Range
'These are the ranges that go into the Lex Delivery Charrt (they come from the Delivery STN by Week Sheet)
Set FirstRange = Sheet2.Range(myValueRowLexWoodchip)
Set SecondRange = Sheet2.Range(myValueRowLexSawdust)
Set ThirdRange = Sheet2.Range(myValueRowTotalDelivery)
Set FourthRange = Sheet2.Range(myValueRowConsumption)
Set FifthRange = Sheet2.Range(myValueRowClosingStock)
 
'This edits the Lex Deliveries Chart (This includes multiple (Y)Ranges/Series, their names are generated here as well)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Union(FirstRange, SecondRange, ThirdRange, FourthRange, FifthRange)
'This gives the names of the Ranges generated before
ActiveChart.SeriesCollection(1).Name = "Lex Woodchip"
ActiveChart.SeriesCollection(2).Name = "Lex Sawdust"
ActiveChart.SeriesCollection(3).Name = "Total Delivery"
ActiveChart.SeriesCollection(4).Name = "Consumption"
ActiveChart.SeriesCollection(5).Name = "Closing Stock"
'This stores the x-values for the Lex Deliveries Chart
ActiveChart.SeriesCollection(1).XValues = myValueRowXAxisSecond
'End the Program
End Sub