I wish i could but this Workbook is extremely private in my company. I ended up shortening the length of the worksheet_selectionchange code and use the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("Q1").Value = "April" Then
Range("R2").Value = "=sum('Project Reporting Summary'!N5)"
Range("S2").Value = "=sum('Project Reporting Summary'!N21)"
Range("T2").Value = "=sum('Project Reporting Summary'!N36)"
Range("U2").Value = "=sum('Project Reporting Summary'!N51)"
Range("V2").Value = "=sum('Project Reporting Summary'!N66)"
Range("W2").Value = "=sum('Project Reporting Summary'!N81)"
Range("X2").Value = "=sum('Project Reporting Summary'!N96)"
Range("Y2").Value = "=sum('Project Reporting Summary'!N111)"
Range("R3").Value = "=sum('Project Reporting Summary'!N9)"
Range("S3").Value = "=sum('Project Reporting Summary'!N25)"
Range("T3").Value = "=sum('Project Reporting Summary'!N40)"
Range("U3").Value = "=sum('Project Reporting Summary'!N55)"
Range("V3").Value = "=sum('Project Reporting Summary'!N70)"
Range("W3").Value = "=sum('Project Reporting Summary'!N85)"
Range("X3").Value = "=sum('Project Reporting Summary'!N100)"
Range("Y3").Value = "=sum('Project Reporting Summary'!N115)"
Range("R4").Value = "=sum('Project Reporting Summary'!N4)"
Range("S4").Value = "=sum('Project Reporting Summary'!N19)"
Range("T4").Value = "=sum('Project Reporting Summary'!N34)"
Range("U4").Value = "=sum('Project Reporting Summary'!N49)"
Range("V4").Value = "=sum('Project Reporting Summary'!N64)"
Range("W4").Value = "=sum('Project Reporting Summary'!N81)"
Range("X4").Value = "=sum('Project Reporting Summary'!N94)"
Range("Y4").Value = "=sum('Project Reporting Summary'!N109)"
Range("R5").Value = "=sum('Project Reporting Summary'!N7)"
Range("S5").Value = "=sum('Project Reporting Summary'!N23)"
Range("T5").Value = "=sum('Project Reporting Summary'!N38)"
Range("U5").Value = "=sum('Project Reporting Summary'!N53)"
Range("V5").Value = "=sum('Project Reporting Summary'!N68)"
Range("W5").Value = "=sum('Project Reporting Summary'!N83)"
Range("X5").Value = "=sum('Project Reporting Summary'!N98)"
Range("Y5").Value = "=sum('Project Reporting Summary'!N113)"
ElseIf Range("Q1").Value = "May" Then
Range("R2").Value = "=sum('Project Reporting Summary'!N5:O5)"
Range("S2").Value = "=sum('Project Reporting Summary'!N21:O21)"
Range("T2").Value = "=sum('Project Reporting Summary'!N36:O36)"
Range("U2").Value = "=sum('Project Reporting Summary'!N51:O51)"
Range("V2").Value = "=sum('Project Reporting Summary'!N66:O66)"
Range("W2").Value = "=sum('Project Reporting Summary'!N81:O81)"
Range("X2").Value = "=sum('Project Reporting Summary'!N96:O96)"
Range("Y2").Value = "=sum('Project Reporting Summary'!N111:O111)"
Range("R3").Value = "=sum('Project Reporting Summary'!N9:O9)"
Range("S3").Value = "=sum('Project Reporting Summary'!N25:O25)"
Range("T3").Value = "=sum('Project Reporting Summary'!N40:O40)"
Range("U3").Value = "=sum('Project Reporting Summary'!N55:O55)"
Range("V3").Value = "=sum('Project Reporting Summary'!N70:O70)"
Range("W3").Value = "=sum('Project Reporting Summary'!N85:O85)"
Range("X3").Value = "=sum('Project Reporting Summary'!N100:O100)"
Range("Y3").Value = "=sum('Project Reporting Summary'!N115:O115)"
Range("R4").Value = "=sum('Project Reporting Summary'!N4:O4)"
Range("S4").Value = "=sum('Project Reporting Summary'!N19:O19)"
Range("T4").Value = "=sum('Project Reporting Summary'!N34:O34)"
Range("U4").Value = "=sum('Project Reporting Summary'!N49:O49)"
Range("V4").Value = "=sum('Project Reporting Summary'!N64:O64)"
Range("W4").Value = "=sum('Project Reporting Summary'!N81:O81)"
Range("X4").Value = "=sum('Project Reporting Summary'!N94:O94)"
Range("Y4").Value = "=sum('Project Reporting Summary'!N109:O109)"
Range("R5").Value = "=sum('Project Reporting Summary'!N7:O7)"
Range("S5").Value = "=sum('Project Reporting Summary'!N23:O23)"
Range("T5").Value = "=sum('Project Reporting Summary'!N38:O38)"
Range("U5").Value = "=sum('Project Reporting Summary'!N53:O53)"
Range("V5").Value = "=sum('Project Reporting Summary'!N68:O68)"
Range("W5").Value = "=sum('Project Reporting Summary'!N83:O83)"
Range("X5").Value = "=sum('Project Reporting Summary'!N98:O98)"
Range("Y5").Value = "=sum('Project Reporting Summary'!N113:O113)"
ElseIf Range("Q1").Value = "June" Then
Range("R2").Value = "=sum('Project Reporting Summary'!N5:P5)"
Range("S2").Value = "=sum('Project Reporting Summary'!N21:P21)"
Range("T2").Value = "=sum('Project Reporting Summary'!N36:P36)"
Range("U2").Value = "=sum('Project Reporting Summary'!N51:P51)"
Range("V2").Value = "=sum('Project Reporting Summary'!N66:P66)"
Range("W2").Value = "=sum('Project Reporting Summary'!N81:P81)"
Range("X2").Value = "=sum('Project Reporting Summary'!N96:P96)"
Range("Y2").Value = "=sum('Project Reporting Summary'!N111:P111)"
Range("R3").Value = "=sum('Project Reporting Summary'!N9:P9)"
Range("S3").Value = "=sum('Project Reporting Summary'!N25:P25)"
Range("T3").Value = "=sum('Project Reporting Summary'!N40:P40)"
Range("U3").Value = "=sum('Project Reporting Summary'!N55:P55)"
Range("V3").Value = "=sum('Project Reporting Summary'!N70:P70)"
Range("W3").Value = "=sum('Project Reporting Summary'!N85:P85)"
Range("X3").Value = "=sum('Project Reporting Summary'!N100:P100)"
Range("Y3").Value = "=sum('Project Reporting Summary'!N115:P115)"
Range("R4").Value = "=sum('Project Reporting Summary'!N4:P4)"
Range("S4").Value = "=sum('Project Reporting Summary'!N19:P19)"
Range("T4").Value = "=sum('Project Reporting Summary'!N34:P34)"
Range("U4").Value = "=sum('Project Reporting Summary'!N49:P49)"
Range("V4").Value = "=sum('Project Reporting Summary'!N64:P64)"
Range("W4").Value = "=sum('Project Reporting Summary'!N81:P81)"
Range("X4").Value = "=sum('Project Reporting Summary'!N94:P94)"
Range("Y4").Value = "=sum('Project Reporting Summary'!N109:P109)"
Range("R5").Value = "=sum('Project Reporting Summary'!N7:P7)"
Range("S5").Value = "=sum('Project Reporting Summary'!N23:P23)"
Range("T5").Value = "=sum('Project Reporting Summary'!N38:P38)"
Range("U5").Value = "=sum('Project Reporting Summary'!N53:P53)"
Range("V5").Value = "=sum('Project Reporting Summary'!N68:P68)"
Range("W5").Value = "=sum('Project Reporting Summary'!N83:P83)"
Range("X5").Value = "=sum('Project Reporting Summary'!N98:P98)"
Range("Y5").Value = "=sum('Project Reporting Summary'!N113:P113)"
End If
End Sub
and put this on a userform with an accept button and a combobox
Private Sub CommandButton1_Click()
If ComboBox1.Value = "April" Then
Sheets("Data").Activate
With Range("Q1")
.Select
.Value = "April"
End With
SendKeys "{Enter}"
Range("Q2").Select
Charts("Totals By Site End of Month").Activate
ActiveChart.ChartTitle.Text = "Totals Per Site End of April"
ElseIf ComboBox1.Value = "May" Then
Sheets("Data").Activate
With Range("Q1")
.Select
.Value = "May"
End With
SendKeys "{Enter}"
Range("Q2").Select
Charts("Totals By Site End of Month").Activate
ActiveChart.ChartTitle.Text = "Totals Per Site End of May"
ElseIf ComboBox1.Value = "June" Then
Sheets("Data").Activate
With Range("Q1")
.Select
.Value = "June"
End With
SendKeys "{Enter}"
Range("Q2").Select
Charts("Totals By Site End of Month").Activate
ActiveChart.ChartTitle.Text = "Totals Per Site End of June"
ElseIf ComboBox1.Value = "July" Then
Sheets("Data").Activate
With Range("Q1")
.Select
.Value = "July"
End With
SendKeys "{Enter}"
Range("Q2").Select
Charts("Totals By Site End of Month").Activate
ActiveChart.ChartTitle.Text = "Totals Per Site End of July"
ElseIf ComboBox1.Value = "August" Then
Sheets("Data").Activate
With Range("Q1")
.Select
.Value = "August"
End With
SendKeys "{Enter}"
Range("Q2").Select
Charts("Totals By Site End of Month").Activate
ActiveChart.ChartTitle.Text = "Totals Per Site End of August"
ElseIf ComboBox1.Value = "September" Then
Sheets("Data").Activate
With Range("Q1")
.Select
.Value = "September"
End With
SendKeys "{Enter}"
Range("Q2").Select
Charts("Totals By Site End of Month").Activate
ActiveChart.ChartTitle.Text = "Totals Per Site End of September"
End If
Unload UserForm1
End Sub
Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = Sheets("Data").Range("Q1").Validation.Formula1
Me.ComboBox1.Value = Sheets("Data").Range("Q1")
End Sub
This works for me. Took out the July, August and September code on the selectionchange cause there were too many characters.
Bookmarks