Private Sub ClearScorecardButton_Click()
Dim x As Integer
x = 13
With ActiveSheet
.Unprotect ("1212")
'-------------------------------------------
'Clear every 8th cell in columns j, r, v, ad
Do While x <= 389
.Range("J" & x & ":R" & x).ClearContents
.Range("V" & x & ":AD" & x).ClearContents
x = x + 8
Loop
'-------------------------------------------
Range("J13").Select
If CommandButton1.BackColor = vbBlue Then
CommandButton1.BackColor = vbGreen
Else
CommandButton1.BackColor = vbBlue
End If
If CommandButton2.BackColor = vbGreen Then
CommandButton2.BackColor = vbBlue
Else
CommandButton2.BackColor = vbGreen
End If
If CommandButton3.BackColor = vbGreen Then
CommandButton3.BackColor = vbBlue
Else
CommandButton3.BackColor = vbGreen
End If
If CommandButton4.BackColor = vbGreen Then
CommandButton4.BackColor = vbBlue
Else
CommandButton4.BackColor = vbGreen
End If
If CommandButton5.BackColor = vbGreen Then
CommandButton5.BackColor = vbBlue
Else
CommandButton5.BackColor = vbGreen
End If
If CommandButton6.BackColor = vbGreen Then
CommandButton6.BackColor = vbBlue
Else
CommandButton6.BackColor = vbGreen
End If
If CommandButton7.BackColor = vbGreen Then
CommandButton7.BackColor = vbBlue
Else
CommandButton7.BackColor = vbGreen
End If
If CommandButton8.BackColor = vbGreen Then
CommandButton8.BackColor = vbBlue
Else
CommandButton8.BackColor = vbGreen
End If
If CommandButton9.BackColor = vbGreen Then
CommandButton9.BackColor = vbBlue
Else
CommandButton9.BackColor = vbGreen
End If
If CommandButton10.BackColor = vbGreen Then
CommandButton10.BackColor = vbBlue
Else
CommandButton10.BackColor = vbGreen
End If
If CommandButton11.BackColor = vbGreen Then
CommandButton11.BackColor = vbBlue
Else
CommandButton11.BackColor = vbGreen
End If
If CommandButton12.BackColor = vbGreen Then
CommandButton12.BackColor = vbBlue
Else
CommandButton12.BackColor = vbGreen
End If
If CommandButton13.BackColor = vbGreen Then
CommandButton13.BackColor = vbBlue
Else
CommandButton13.BackColor = vbGreen
End If
If CommandButton14.BackColor = vbGreen Then
CommandButton14.BackColor = vbBlue
Else
CommandButton14.BackColor = vbGreen
End If
If CommandButton15.BackColor = vbGreen Then
CommandButton15.BackColor = vbBlue
Else
CommandButton15.BackColor = vbGreen
End If
If CommandButton16.BackColor = vbGreen Then
CommandButton16.BackColor = vbBlue
Else
CommandButton16.BackColor = vbGreen
End If
If CommandButton17.BackColor = vbGreen Then
CommandButton17.BackColor = vbBlue
Else
CommandButton17.BackColor = vbGreen
End If
If CommandButton18.BackColor = vbGreen Then
CommandButton18.BackColor = vbBlue
Else
CommandButton18.BackColor = vbGreen
End If
If CommandButton19.BackColor = vbGreen Then
CommandButton19.BackColor = vbBlue
Else
CommandButton19.BackColor = vbGreen
End If
If CommandButton20.BackColor = vbGreen Then
CommandButton20.BackColor = vbBlue
Else
CommandButton20.BackColor = vbGreen
End If
If CommandButton21.BackColor = vbGreen Then
CommandButton21.BackColor = vbBlue
Else
CommandButton21.BackColor = vbGreen
End If
If CommandButton22.BackColor = vbGreen Then
CommandButton22.BackColor = vbBlue
Else
CommandButton22.BackColor = vbGreen
End If
If CommandButton23.BackColor = vbGreen Then
CommandButton23.BackColor = vbBlue
Else
CommandButton23.BackColor = vbGreen
End If
If CommandButton24.BackColor = vbGreen Then
CommandButton24.BackColor = vbBlue
Else
CommandButton24.BackColor = vbGreen
End If
If CommandButton25.BackColor = vbGreen Then
CommandButton25.BackColor = vbBlue
Else
CommandButton25.BackColor = vbGreen
End If
If CommandButton26.BackColor = vbGreen Then
CommandButton26.BackColor = vbBlue
Else
CommandButton26.BackColor = vbGreen
End If
If CommandButton27.BackColor = vbGreen Then
CommandButton27.BackColor = vbBlue
Else
CommandButton27.BackColor = vbGreen
End If
If CommandButton28.BackColor = vbGreen Then
CommandButton28.BackColor = vbBlue
Else
CommandButton28.BackColor = vbGreen
End If
If CommandButton29.BackColor = vbGreen Then
CommandButton29.BackColor = vbBlue
Else
CommandButton29.BackColor = vbGreen
End If
If CommandButton30.BackColor = vbGreen Then
CommandButton30.BackColor = vbBlue
Else
CommandButton30.BackColor = vbGreen
End If
If CommandButton31.BackColor = vbGreen Then
CommandButton31.BackColor = vbBlue
Else
CommandButton31.BackColor = vbGreen
End If
If CommandButton32.BackColor = vbGreen Then
CommandButton32.BackColor = vbBlue
Else
CommandButton32.BackColor = vbGreen
End If
If CommandButton33.BackColor = vbGreen Then
CommandButton33.BackColor = vbBlue
Else
CommandButton33.BackColor = vbGreen
End If
If CommandButton34.BackColor = vbGreen Then
CommandButton34.BackColor = vbBlue
Else
CommandButton34.BackColor = vbGreen
End If
If CommandButton35.BackColor = vbGreen Then
CommandButton35.BackColor = vbBlue
Else
CommandButton35.BackColor = vbGreen
End If
If CommandButton36.BackColor = vbGreen Then
CommandButton36.BackColor = vbBlue
Else
CommandButton36.BackColor = vbGreen
End If
If CommandButton37.BackColor = vbGreen Then
CommandButton37.BackColor = vbBlue
Else
CommandButton37.BackColor = vbGreen
End If
If CommandButton38.BackColor = vbGreen Then
CommandButton38.BackColor = vbBlue
Else
CommandButton38.BackColor = vbGreen
End If
If CommandButton39.BackColor = vbGreen Then
CommandButton39.BackColor = vbBlue
Else
CommandButton39.BackColor = vbGreen
End If
If CommandButton40.BackColor = vbGreen Then
CommandButton40.BackColor = vbBlue
Else
CommandButton40.BackColor = vbGreen
End If
If CommandButton41.BackColor = vbGreen Then
CommandButton41.BackColor = vbBlue
Else
CommandButton41.BackColor = vbGreen
End If
If CommandButton42.BackColor = vbGreen Then
CommandButton42.BackColor = vbBlue
Else
CommandButton42.BackColor = vbGreen
End If
If CommandButton43.BackColor = vbGreen Then
CommandButton43.BackColor = vbBlue
Else
CommandButton43.BackColor = vbGreen
End If
If CommandButton44.BackColor = vbGreen Then
CommandButton44.BackColor = vbBlue
Else
CommandButton44.BackColor = vbGreen
End If
If CommandButton45.BackColor = vbGreen Then
CommandButton45.BackColor = vbBlue
Else
CommandButton45.BackColor = vbGreen
End If
If CommandButton46.BackColor = vbGreen Then
CommandButton46.BackColor = vbBlue
Else
CommandButton46.BackColor = vbGreen
End If
If CommandButton47.BackColor = vbGreen Then
CommandButton47.BackColor = vbBlue
Else
CommandButton47.BackColor = vbGreen
End If
If CommandButton48.BackColor = vbGreen Then
CommandButton48.BackColor = vbBlue
Else
CommandButton48.BackColor = vbGreen
End If
.Protect ("1212")
End With
End Sub
Private Sub CommandButton1_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BC401:BC435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton1.BackColor = vbGreen Then
CommandButton1.BackColor = vbBlue
Else
CommandButton1.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton2_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BD401:BD435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton2.BackColor = vbGreen Then
CommandButton2.BackColor = vbBlue
Else
CommandButton2.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton3_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BE401:BE435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton3.BackColor = vbGreen Then
CommandButton3.BackColor = vbBlue
Else
CommandButton3.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton4_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BF401:BF435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton4.BackColor = vbGreen Then
CommandButton4.BackColor = vbBlue
Else
CommandButton4.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton5_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BG401:BG435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton5.BackColor = vbGreen Then
CommandButton5.BackColor = vbBlue
Else
CommandButton5.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton6_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BH401:BH435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton6.BackColor = vbGreen Then
CommandButton6.BackColor = vbBlue
Else
CommandButton6.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton7_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BI401:BI435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton7.BackColor = vbGreen Then
CommandButton7.BackColor = vbBlue
Else
CommandButton7.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton8_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BJ401:BJ435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton8.BackColor = vbGreen Then
CommandButton8.BackColor = vbBlue
Else
CommandButton8.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton9_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BK401:BK435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton9.BackColor = vbGreen Then
CommandButton9.BackColor = vbBlue
Else
CommandButton9.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton10_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BL401:BL435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton10.BackColor = vbGreen Then
CommandButton10.BackColor = vbBlue
Else
CommandButton10.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton11_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BM401:BM435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton11.BackColor = vbGreen Then
CommandButton11.BackColor = vbBlue
Else
CommandButton11.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton12_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BN401:BN435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton12.BackColor = vbGreen Then
CommandButton12.BackColor = vbBlue
Else
CommandButton12.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton13_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BO401:BO435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton13.BackColor = vbGreen Then
CommandButton13.BackColor = vbBlue
Else
CommandButton13.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton14_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BP401:BP435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton14.BackColor = vbGreen Then
CommandButton14.BackColor = vbBlue
Else
CommandButton14.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton15_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BQ401:BQ435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton15.BackColor = vbGreen Then
CommandButton15.BackColor = vbBlue
Else
CommandButton15.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton16_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BR401:BR435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton16.BackColor = vbGreen Then
CommandButton16.BackColor = vbBlue
Else
CommandButton16.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton17_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BS401:BS435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton17.BackColor = vbGreen Then
CommandButton17.BackColor = vbBlue
Else
CommandButton17.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton18_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BT401:BT435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton18.BackColor = vbGreen Then
CommandButton18.BackColor = vbBlue
Else
CommandButton18.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton19_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BU401:BU435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton19.BackColor = vbGreen Then
CommandButton19.BackColor = vbBlue
Else
CommandButton19.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton20_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BV401:BV435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton20.BackColor = vbGreen Then
CommandButton20.BackColor = vbBlue
Else
CommandButton20.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton21_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BW401:BW435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton21.BackColor = vbGreen Then
CommandButton21.BackColor = vbBlue
Else
CommandButton21.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton22_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BX401:BX435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton22.BackColor = vbGreen Then
CommandButton22.BackColor = vbBlue
Else
CommandButton22.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton23_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BY401:BY435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton23.BackColor = vbGreen Then
CommandButton23.BackColor = vbBlue
Else
CommandButton23.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton24_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("BZ401:BZ435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton24.BackColor = vbGreen Then
CommandButton24.BackColor = vbBlue
Else
CommandButton24.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton25_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CA401:CA435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton25.BackColor = vbGreen Then
CommandButton25.BackColor = vbBlue
Else
CommandButton25.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton26_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CB401:CB435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton26.BackColor = vbGreen Then
CommandButton26.BackColor = vbBlue
Else
CommandButton26.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton27_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CC401:CC435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton27.BackColor = vbGreen Then
CommandButton27.BackColor = vbBlue
Else
CommandButton27.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton28_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CD401:CD435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton28.BackColor = vbGreen Then
CommandButton28.BackColor = vbBlue
Else
CommandButton28.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton29_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CE401:CE435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton29.BackColor = vbGreen Then
CommandButton29.BackColor = vbBlue
Else
CommandButton29.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton30_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CF401:CF435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton30.BackColor = vbGreen Then
CommandButton30.BackColor = vbBlue
Else
CommandButton30.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton31_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CG401:CG435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton31.BackColor = vbGreen Then
CommandButton31.BackColor = vbBlue
Else
CommandButton31.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton32_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CH401:CH435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton32.BackColor = vbGreen Then
CommandButton32.BackColor = vbBlue
Else
CommandButton32.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton33_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CI401:CI435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton33.BackColor = vbGreen Then
CommandButton33.BackColor = vbBlue
Else
CommandButton33.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton34_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CJ401:CJ435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton34.BackColor = vbGreen Then
CommandButton34.BackColor = vbBlue
Else
CommandButton34.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton35_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CK401:CK435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton35.BackColor = vbGreen Then
CommandButton35.BackColor = vbBlue
Else
CommandButton35.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton36_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CL401:CL435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton36.BackColor = vbGreen Then
CommandButton36.BackColor = vbBlue
Else
CommandButton36.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton37_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CM401:CM435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton37.BackColor = vbGreen Then
CommandButton37.BackColor = vbBlue
Else
CommandButton37.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton38_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CN401:CN435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton38.BackColor = vbGreen Then
CommandButton38.BackColor = vbBlue
Else
CommandButton38.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton39_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CO401:CO435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton39.BackColor = vbGreen Then
CommandButton39.BackColor = vbBlue
Else
CommandButton39.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton40_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CP401:CP435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton40.BackColor = vbGreen Then
CommandButton40.BackColor = vbBlue
Else
CommandButton40.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton41_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CQ401:CQ435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton41.BackColor = vbGreen Then
CommandButton41.BackColor = vbBlue
Else
CommandButton41.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton42_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CR401:CR435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton42.BackColor = vbGreen Then
CommandButton42.BackColor = vbBlue
Else
CommandButton42.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton43_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CS401:CS435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton43.BackColor = vbGreen Then
CommandButton43.BackColor = vbBlue
Else
CommandButton43.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton44_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CT401:CT435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton44.BackColor = vbGreen Then
CommandButton44.BackColor = vbBlue
Else
CommandButton44.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton45_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CU401:CU435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton45.BackColor = vbGreen Then
CommandButton45.BackColor = vbBlue
Else
CommandButton45.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton46_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CV401:CV435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton46.BackColor = vbGreen Then
CommandButton46.BackColor = vbBlue
Else
CommandButton46.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton47_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CW401:CW435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton47.BackColor = vbGreen Then
CommandButton47.BackColor = vbBlue
Else
CommandButton47.BackColor = vbGreen
End If
End Sub
Private Sub CommandButton48_Click()
Dim destrange As Range
Dim smallrng As Range
Application.ScreenUpdating = False
For Each smallrng In Sheets("Scorecard"). _
Range("CX401:CX435").Areas
Set destrange = Sheets("database").Range("A" & _
LastRow(Sheets("database")) + 1)
smallrng.Copy
destrange.PasteSpecial xlPasteValues, , False, True
Application.CutCopyMode = False
Next smallrng
Application.ScreenUpdating = True
If CommandButton48.BackColor = vbGreen Then
CommandButton48.BackColor = vbBlue
Else
CommandButton48.BackColor = vbGreen
End If
End Sub
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=True).Row
On Error GoTo 0
End Function
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'The 1st method is coded to launch when a curtain cell is selected
If Target.Address = "$AD$4:$AH$4" Or Target.Address = "$AD$4:$AH$4" Or Target.Address = "$AD$4:$AH$4" Then
CalendarFrm.Show
End If
'another method is to have it show if a cell that is formatted as a date is selected...see below
If Target.NumberFormat = "m/d/yy;@" Then
CalendarFrm.Show
End If
'another method is using Select Case...see below
Select Case Target.NumberFormat
Case Is = "m/d/yy", "m/d/yyyy", "m/d/yy", "mm/dd/yy", "yyyy-mmm-dd", "d/m/yy;@", "dd/mm/yy;@", "dd-mmm", "dd-mmm-yy", "dd/mm/yyyy;@", "yyyy-mm-dd;@", "m/dd/yy;@", "m/d/yyyy;@", "mm/dd/yy;@", "yy-mm-dd;@", "[$-409]d-mmm-yyyy;@", "[$-409]d-mmm-yy;@", "[$-409]dd-mmm-yy;@", "[$-409]mmmm d, yyyy;@", "[$-1009]d-mmm-yy;@", "[$-1009]mmmm d, yyyy;@", "[$-F800]dddd, mmmm dd, yyyy"
CalendarFrm.Show
'Case Else
' MsgBox "Not a valid date format!"
End Select
End Sub
