forked from christwellman/VBAMacros
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmetrics PMO cleanup script.vb
207 lines (163 loc) · 12.5 KB
/
metrics PMO cleanup script.vb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
Sub CleanData()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim checkarea As Range
Dim checkrow As Range
Dim StartRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
' Delete unneccssary sheets.
Application.DisplayAlerts = False
On Error Resume Next
'ActiveWorkbook.Worksheets("LOVs").Delete
'ActiveWorkbook.Worksheets("Complete - Presales - Scoping").Delete
'ActiveWorkbook.Worksheets("Cold Projects").Delete
'ActiveWorkbook.Worksheets("Closed Projects").Delete
'ActiveWorkbook.Worksheets("Project Tracking - GPAGE").Delete
'ActiveWorkbook.Worksheets("Archive Desk Complete").Delete
'ActiveWorkbook.Worksheets("Archive Cold Projects").Delete
'ActiveWorkbook.Worksheets("Archive Closed Projects").Delete
'ActiveWorkbook.Worksheets("Project Pipeline").Delete
'ActiveWorkbook.Worksheets("New WM Mapping").Delete
On Error GoTo 0
' Choose Sheet to Clean
destshname = InputBox(Prompt:="Which worksheet do you want to clean?", Title:="Clean which sheet?", Default:="")
Set DestSh = ActiveWorkbook.Worksheets(destshname)
'DestSh = "Project Pipeline"
' Fill in the start row.--- Not Used See below check to get seccond row of data
'If DestSh.Name = "Project Pipeline" Then
StartRow = InputBox(Prompt:="Which row would you like to start checking at?", Title:="Start Row?", Default:=2)
'Else
'StartRow = 2
'End If
Last = Lastrow(DestSh)
Firstrow = StartRow
Set checkrange = Range(Cells(Firstrow, "A"), Cells(Last, "AP"))
'MsgBox ("Range " & checkrange.Address)
With DestSh
.DisplayPageBreaks = False
For Each checkrow In checkrange.Rows
' Check if request preceeds Desk
If .Cells(checkrow.Row, "D").Value = "Before Desk" Or .Cells(checkrow.Row, "D").Value < CDate("8/8/2011") Then
Else
'Proceed with data checks if request came in after desk was established
''''''''''''''''''''
'Check request Date
'''''''''''''''''''''''''''''
If IsDate(.Cells(checkrow.Row, "D")) = False Then
.Cells(checkrow.Row, "D").Value = InputBox(Prompt:="Please provide a valid request date for customer: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="Request Date?", Default:=.Cells(checkrow.Row, "D").Value)
End If
''''''''''''''''''''''''''''''''''''''
'Check PID string length for valid PID
''''''''''''''''''''''''''''''''''''''
If Len(.Cells(checkrow.Row, "F").Value) = 6 Then
Else
.Cells(checkrow.Row, "F").Value = InputBox(Prompt:="Please provide a PID for: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="PID?", Default:=.Cells(checkrow.Row, "F").Value)
End If
''''''''''''''''''''''''''''''''''''''
'Check PID Status
''''''''''''''''''''''''''''''''''''''
If .Cells(checkrow.Row, "G").Value = "Pipeline" Or .Cells(checkrow.Row, "G").Value = "Presales" Or .Cells(checkrow.Row, "G").Value = "Active" Or .Cells(checkrow.Row, "G").Value = "Cancelled" Or .Cells(checkrow.Row, "G").Value = "Closed" Or .Cells(checkrow.Row, "G").Value = "Delivery Close" Or .Cells(checkrow.Row, "G").Value = "On Hold" Or .Cells(checkrow.Row, "G").Value = "Not Available" Then
Else
.Cells(checkrow.Row, "G").Value = InputBox(Prompt:="Please provide a PID Status for: " & .Cells(checkrow.Row, "F"), Title:="PID Status?", Default:=.Cells(checkrow.Row, "G").Value)
End If
''''''''''''''''''''''''''''''''''''''
'Check Technology Status
''''''''''''''''''''''''''''''''''''''
If .Cells(checkrow.Row, "K").Value = "CIAC" Or .Cells(checkrow.Row, "K").Value = "UCS" Or .Cells(checkrow.Row, "K").Value = "DCN" Or .Cells(checkrow.Row, "K").Value = "SAN" Or .Cells(checkrow.Row, "K").Value = "VDI" Then
Else
.Cells(checkrow.Row, "K").Value = InputBox(Prompt:="Please provide a Valid Technology for: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="DCV Technology?", Default:=.Cells(checkrow.Row, "L").Value)
End If
''''''''''''''''''''''''''''''''''''''
'Check Service Type
''''''''''''''''''''''''''''''''''''''
If IsEmpty(.Cells(checkrow.Row, "L").Value) = False Then
Else
.Cells(checkrow.Row, "L").Value = InputBox(Prompt:="Please provide a Service Type for: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="Service Type?", Default:=.Cells(checkrow.Row, "L").Value)
End If
''''''''''''''''''''''''''''''''''''''
'Check Request Type
''''''''''''''''''''''''''''''''''''''
If .Cells(checkrow.Row, "Q").Value = "Scoping" Or .Cells(checkrow.Row, "Q").Value = "Presales" Or .Cells(checkrow.Row, "Q").Value = "Delivery" Or .Cells(checkrow.Row, "Q").Value = "SME" Then
Else
.Cells(checkrow.Row, "Q").Value = InputBox(Prompt:="Please provide a request Type for: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="Request Type?", Default:=.Cells(checkrow.Row, "Q").Value)
End If
''''''''''''''''''''''''''''''''''''''''
'Check Project Type
''''''''''''''''''''''''''''''''''''''''
If .Cells(checkrow.Row, "R").Value = "Subscription" Or .Cells(checkrow.Row, "R").Value = "Transaction" Or .Cells(checkrow.Row, "R").Value = "AS Fixed" Or .Cells(checkrow.Row, "R").Value = "CAP" Or .Cells(checkrow.Row, "R").Value = "Unknown" Then
Else
.Cells(checkrow.Row, "R").Value = InputBox(Prompt:="Please provide a Valid Project Type: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="Project Type?", Default:=.Cells(checkrow.Row, "R").Value)
End If
'''''''''''''''''''''''''''''
'Check for Work Manager
'''''''''''''''''''''''''''''
If IsEmpty(.Cells(checkrow.Row, "S").Value) = False Then
Else
.Cells(checkrow.Row, "S").Value = InputBox(Prompt:="Please provide a Work Manager for: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="WM?", Default:=.Cells(checkrow.Row, "S").Value)
End If
'''''''''''''''''''''''''''''
'Check for Project Manager/DCPM
'''''''''''''''''''''''''''''''
If IsEmpty(.Cells(checkrow.Row, "T").Value) = False Then
Else
.Cells(checkrow.Row, "T").Value = InputBox(Prompt:="Please provide a Project Manager/DCPM for: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="DCPM?", Default:=.Cells(checkrow.Row, "T").Value)
End If
''''''''''''''''''''''''''''''''
'Services Revenue should be updated via PID health Report and is ommitted here intentionally for now
''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''
'Dynamically Update DCPM Status
'''''''''''''''''''''''''''''''''''''''''''''''''''
If (.Cells(checkrow.Row, "G").Value = "Active" And IsEmpty(.Cells(checkrow.Row, "T").Value) = True) Or (.Cells(checkrow.Row, "G").Value = "Pipeline") Then
.Cells(checkrow.Row, "AI").Value = "Pending Assignment"
ElseIf .Cells(checkrow.Row, "G").Value = "Presales" And IsEmpty(.Cells(checkrow.Row, "S").Value) = False Then
.Cells(checkrow.Row, "AI").Value = "In Progress"
ElseIf .Cells(checkrow.Row, "G").Value = "On Hold" Then
.Cells(checkrow.Row, "AI").Value = "On Hold"
ElseIf .Cells(checkrow.Row, "G").Value = "Delivery Close" Then
.Cells(checkrow.Row, "AI").Value = "Delivery Close"
ElseIf .Cells(checkrow.Row, "G").Value = "Cancelled" Then
.Cells(checkrow.Row, "AI").Value = "Complete"
ElseIf .Cells(checkrow.Row, "G").Value = "Closed" Then
.Cells(checkrow.Row, "AI").Value = "Closed"
Else
.Cells(checkrow.Row, "AI").Value = InputBox(Prompt:="Please provide a valid DCPM Status for: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="DCPM Status?", Default:=.Cells(checkrow.Row, "I").Value)
End If
''''''''''''''''''''''''''''''''''''
' Check Follow Up Date
'''''''''''''''''''''''''''''''''''''''
If .Cells(checkrow.Row, "AK").Value = "Fixed" Then
ElseIf IsDate(.Cells(checkrow.Row, "AK")) = False Then
.Cells(checkrow.Row, "AK").Value = InputBox(Prompt:="Please provide a valid follow-up date for customer: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="Initial Follow up Date?", Default:=.Cells(checkrow.Row, "AK").Value)
End If
''''''''''''''''''''''''''''''''''''
' Check DCPM Assigned Date
'''''''''''''''''''''''''''''''''''''''
If IsEmpty(.Cells(checkrow.Row, "AL").Value) = True And IsEmpty(.Cells(checkrow.Row, "T").Value) = False Then
.Cells(checkrow.Row, "AL").Value = InputBox(Prompt:="Please provide a valid DCPM Assignment date for customer: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="PM Assigned Date?", Default:=.Cells(checkrow.Row, "AL").Value)
End If
''''''''''''''''''''''''''''''''''''
' Check Closure Date
'''''''''''''''''''''''''''''''''''''''
If IsEmpty(.Cells(checkrow.Row, "AP").Value) = True And .Cells(checkrow.Row, "G").Value = "Closed" Then
.Cells(checkrow.Row, "AP").Value = InputBox(Prompt:="Please provide a valid project close date for customer: " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "H") & " " & .Cells(checkrow.Row, "F"), Title:="Project Close Date?", Default:=.Cells(checkrow.Row, "AP").Value)
End If
End If
.Cells(checkrow.Row, "AY").Value = Now
Next
End With
' AutoFit the column width in the summary sheet.
'DestSh.Columns.AutoFit
'DestSh.Rows.Height = 35
With Application
.Calculation = CalcMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub