Hi All,
I would need help for me to understand how I could manage populating many charts with sorted data based on different criteria like Route, Carrier, Consignee, Activity ?
e.g. Let's say we have a route PAR-TYO. Let's say that for the same route we use the carrier AF and JL.
i need to create one chart for the cycle time on PAR-TYO with AF and one chart PAR-TYO with JL.
I need to create also another chart with the volume carried on PAR-TYO with AF and another one PAR-TYO with JL.
How could I automated it ?
I have wrote the following code:
Unfortunately my chart are not populated as expected.
In addition for my reporting I will have hundreds of chart to do and i dont think that to write a code with the IF condition will be worth coz i will have to write hundreds of line, and i may face memory issue.
Thanks for your feedback,
Best Regards,
Lomic6
I would need help for me to understand how I could manage populating many charts with sorted data based on different criteria like Route, Carrier, Consignee, Activity ?
e.g. Let's say we have a route PAR-TYO. Let's say that for the same route we use the carrier AF and JL.
i need to create one chart for the cycle time on PAR-TYO with AF and one chart PAR-TYO with JL.
I need to create also another chart with the volume carried on PAR-TYO with AF and another one PAR-TYO with JL.
How could I automated it ?
I have wrote the following code:
Code:
Sub myChartDataLSCSC()
Dim c As Range, typ As String, typ2 As String, typ3 As String, typ4 As String
Dim tr As Long, month As Long, year As Long
Dim output(6)
Dim wsSR As Worksheet, wsCS As Worksheet
Set wsSR = Sheets("LSCSC")
Set wsCS = Sheets("ChartLSCSC")
Application.ScreenUpdating = False
wsSR.Activate
wsSR.Range("A1").AutoFilter Field:=44, Criteria1:="<>"
For Each c In wsSR.Range("AZ:AZ").SpecialCells(12)
tr = c.Row
If tr <> 1 Then
'Output for the Cycle Time
output(1) = wsSR.Cells(tr, "Q").Value
output(2) = wsSR.Cells(tr, "R").Value
output(3) = wsSR.Cells(tr, "S").Value
output(4) = wsSR.Cells(tr, "T").Value
output(5) = wsSR.Cells(tr, "U").Value
output(6) = wsSR.Cells(tr, "V").Value
year = wsSR.Cells(tr, "AK").Value
month = wsSR.Cells(tr, "AL").Value
'"AR" corresponds to the route e.g. PAR-TYO
typ = wsSR.Cells(tr, "AR").Value
'"AP" corresponds to the Shipping plant
typ2 = wsSR.Cells(tr, "AP").Value
'"AQ" corresponds to the Receiving plant
typ3 = wsSR.Cells(tr, "AQ").Value
'"AT" corresponds to the Carrier e.g. AF or JL
typ4 = wsSR.Cells(tr, "AT").Value
If typ = "SIN-TYO" And typ4 = "JL" Then
wsCS.Range("D45").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "SIN-TYO" And typ4 = "AF" Then
wsCS.Range("D97").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
Else
GoTo Exit_Sub
End If
'Output for the volume
output(1) = wsSR.Cells(tr, "W").Value
output(2) = wsSR.Cells(tr, "X").Value
output(3) = wsSR.Cells(tr, "Y").Value
output(4) = wsSR.Cells(tr, "AE").Value
output(5) = wsSR.Cells(tr, "AC").Value
output(6) = wsSR.Cells(tr, "AF").Value
If typ = "SIN-TYO" And typ4 = "JL" Then
wsCS.Range("X45").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
ElseIf typ = "SIN-TYO" And typ4 = "AF" Then
wsCS.Range("X97").Offset(, month).Resize(6, 1) = WorksheetFunction.Transpose(output)
Else
GoTo Exit_Sub
End If
End If
Next
Selection.NumberFormat = "0.0"
Exit_Sub:
wsSR.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub
Unfortunately my chart are not populated as expected.
In addition for my reporting I will have hundreds of chart to do and i dont think that to write a code with the IF condition will be worth coz i will have to write hundreds of line, and i may face memory issue.
Thanks for your feedback,
Best Regards,
Lomic6