Creating Graph that updates

Posts
4
Registration date
Monday August 12, 2019
Status
Member
Last seen
August 13, 2019
- - Latest reply: vcoolio
Posts
1249
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
- Aug 17, 2019 at 10:45 PM
Hello,

I am keeping track of how many work orders are being created each month. On my "Completed Sheet" in column F it has the due dates. I would like to figure out how to calculate that column and create how many wokr orders are completed each month and put into a bar graph to represent it. I also would like it placed on a different sheet. Please help me if you can.

Thank you,





System Configuration: Windows / Chrome 75.0.3770.100
See more 

2 replies

Posts
1249
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
203
0
Thank you
Hello Ryleecone,

Try the following code placed in a standard module and assigned to a button:-

Sub ValuesToGraph()

        Dim i As Variant, j As Variant
        Dim ws As Worksheet: Set ws = Sheets("Completed")
        Dim ws1 As Worksheet: Set ws1 = Sheets("Monthly Graph Completed")
        Dim lr As Long: lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        
Application.ScreenUpdating = False
        
        ws.Range("F2:F" & lr).Copy ws.[X2]   '---->Extract the "Due Date" column(F)over to Column X.
        ws.Range("X2", ws.Range("X" & ws.Rows.Count).End(xlUp)).Sort ws.[X2], 1  '---->Sort in month order (Jan-Dec).
        ws.Range("Y2:Y" & lr) = "=TEXT(X2,""MMMM"")" '---->Extract the month names from Column X to Column Y.
        ws.Range("AA2:AA13") = "=COUNTIF(Y:Y,Z2)"  '---->Counts the number of month entries.
       
'The following extracts the unique month names into Column Z so that duplicates are not included.
j = Application.Transpose(ws.Range("Y2", ws.Range("Y" & ws.Rows.Count).End(xlUp)))
        With CreateObject("Scripting.Dictionary")
            For Each i In j
               .Item(i) = i
            Next
               ws.Cells(2, 26).Resize(.Count) = Application.Transpose(.Keys) '---->Unique month names are placed in Column Z starting in row2.
        End With

ws1.Range("B2:B13").Value = ws.Range("AA2:AA13").Value '---->The month quantities are placed in Column B of the graph sheet.
ws.Columns("X:AA").Clear '---->The helper columns are cleared.

Application.ScreenUpdating = True

End Sub


The code uses four helper columns (X:AA) just for an instant in the "Completed" sheet to achieve the desired result.
If your graph has been set up correctly, then, each time the code is run, the graph will update based on the new entries in Column B of the Graph sheet.

I've added some comments in the code above (green font) which will help you to understand what's going on.

I hope that this helps.

Cheerio,
vcoolio.
Respond to vcoolio
Posts
1249
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
203
0
Thank you
Hello again Ryleecone,

The following amended code would be a better option as it would account for the months not being in order as well:-


Sub ValuesToGraph()

        Dim i As Variant, j As Variant, c As Range
        Dim ws As Worksheet: Set ws = Sheets("Completed")
        Dim ws1 As Worksheet: Set ws1 = Sheets("Monthly Graph Completed")
        Dim lr As Long: lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        
Application.ScreenUpdating = False
        
        ws.Range("F2:F" & lr).Copy ws.[X2]   '---->Extract the "Due Date" column(F)over to Column X.
        ws.Range("Y2:Y" & lr) = "=TEXT(X2,""MMMM"")" '---->Extract the month names from Column X to Column Y.
        ws.Range("AA2:AA13") = "=COUNTIF(Y:Y,Z2)"  '---->Counts the number of month entries.
       
'The following extracts the unique month names into Column Z so that duplicates are not included.
j = Application.Transpose(ws.Range("Y2", ws.Range("Y" & ws.Rows.Count).End(xlUp)))
        With CreateObject("Scripting.Dictionary")
            For Each i In j
               .Item(i) = i
            Next
               ws.Cells(2, 26).Resize(.Count) = Application.Transpose(.Keys) '---->Unique month names are placed in Column Z starting in row2.
        End With
        
'Sending the correct values to B2:B13 of the Graph sheet regardless of the month order in the Completed sheet.
For i = 2 To lr
       For Each c In ws1.Range("A2:A13")
            If c.Value = ws.Cells(i, 26).Value Then
            c.Offset(, 1).Value = ws.Cells(i, 27).Value
            End If
       Next c
Next i

ws.Columns("X:AA").Clear '---->The helper columns are cleared.

Application.ScreenUpdating = True

End Sub


Cheerio,
vcoolio.
Respond to vcoolio