Sub COADataMiner()
'
' COADataMiner Macro
' Pulls Values from Scope Sheets
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim COAWorkbook As Workbook, ScopeSheets As Workbook
Dim Filename As String
Dim File As Integer
Dim r As Long
Dim t As Long
Dim x As Integer
Dim y As Integer
Dim ContractAmount As Double
Dim rscope As Integer
Dim ParentCode As String
Dim ElementCode As Integer
Dim WorkCategory As String
Dim Msg As String
Dim BondCostLoc As Range
Dim BondCost As Double
Dim CDICost As Double
Dim BaseBidLoc As Range
Dim BaseBid As Double
Dim FormatAns As Integer
Dim BondAns As Integer
rscope = 1
s = 0
t = 0
ContractAmount = 0
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Title = "Select files to process"
.Show
If .SelectedItems.Count = 0 Then Exit Sub
Set COAWorkbook = ActiveWorkbook
For File = 1 To .SelectedItems.Count
Filename = .SelectedItems.Item(File)
If Right(Filename, 4) = ".xls" Or Right(Filename, 5) = ".xlsx" Then
Workbooks.Open Filename, 0, True
Set ScopeSheets = ActiveWorkbook
'Scope Sheet is Open
WorkCategory = ScopeSheets.Sheets("Scope").Range("G2").Value
Msg = "Provide Parent Code for Work Category " & WorkCategory & ". (Include 0 at the beginning of the Parent Code)"
Set COAWorkbook = ActiveWorkbook
ParentCode = InputBox(Msg)
ElementCode = 10
FormatAns = MsgBox("Is the contractor being used in the first column on the right", vbYesNo)
Select Case FormatAns
Case vbYes
GoTo Continue
Case vbNo
MsgBox "Sort This Scope Sheet Horizontaly so the contractor being used is in the 1st column on the right."
GoTo Reformat
Continue:
End Select
Set ScopeSheets = ActiveWorkbook
Set BondCostLoc = ScopeSheets.Application.InputBox(Prompt:="Select the Bond/CDI Amount for the contractor used. Do not select the Bond Rate", Type:=8)
BondCost = BondCostLoc.Value
BondAns = MsgBox("Will the contractor be enrolled in CDI?", vbYesNo)
Select Case BondAns
Case vbYes
CDICost = CDICost + BondCost
Case vbNo
ContractAmount = ContractAmount + BondCost
End Select
Set BaseBidLoc = Application.InputBox(Prompt:="Select the Base Bid for the contractor used", Type:=8)
BaseBid = BaseBidLoc.Value
ContractAmount = ContractAmount + BaseBid
Do
Select Case IsEmpty(BaseBidLoc.Offset(rscope, 0))
Case True
GoTo NextCell
Case Else
Select Case IsNumeric(BaseBidLoc.Offset(rscope, 0))
Case True
If BaseBidLoc.Offset(rscope, 0).Interior.Color = 65535 Then
x = BaseBidLoc.Offset(rscope, 0).Value
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s, 0) = x
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s, 0).Font.Bold = False
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s, 0).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s, 0).Interior.Color = 65535
BaseBidLoc.Offset(rscope, -1).Copy COAWorkbook.Sheets("Sheet1").Range("D7").Offset(s, 0)
s = s + 1
t = t + 1
ElseIf BaseBidLoc.Offset(rscope, 0).Font.Bold = True Then
x = BaseBidLoc.Offset(rscope, 0).Value
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s, 0) = x
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s, 0).Font.Bold = True
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s, 0).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
COAWorkbook.Sheets("Sheet1").Range("B7").Offset(s, 0) = ParentCode
COAWorkbook.Sheets("Sheet1").Range("C7").Offset(s, 0) = ParentCode & ".00" & ElementCode & ".00.00"
BaseBidLoc.Offset(rscope, -3).Copy COAWorkbook.Sheets("Sheet1").Range("D7").Offset(s, 0)
ElementCode = ElementCode + 10
s = s + 1
t = t + 1
ElseIf BaseBidLoc.Offset(rscope, 0).Font.Bold = False Then
ContractAmount = ContractAmount + BaseBidLoc.Offset(rscope, 0).Value
End If
Case False
End Select
End Select
NextCell:
rscope = rscope + 1
Loop Until BaseBidLoc.Offset(rscope, 0).Borders(xlEdgeBottom).LineStyle = xlContinuous And BaseBidLoc.Offset(rscope, 0).Borders(xlEdgeBottom).Weight = xlMedium
COAWorkbook.Sheets("Sheet1").Range("D7").Offset(s - t - 1, 0).Value = WorkCategory
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s - t - 1, 0).Value = ContractAmount
COAWorkbook.Sheets("Sheet1").Range("I7").Offset(s - t - 1, 0).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
COAWorkbook.Sheets("Sheet1").Range("C7").Offset(s - t - 1, 0) = ParentCode & ".0000.00.00"
COAWorkbook.Sheets("Sheet1").Range("C7").Offset(s - t - 1, -1) = ParentCode
COAWorkbook.Sheets("Sheet1").Range("D7").Offset(s - t - 2, 0).Value = WorkCategory
COAWorkbook.Sheets("Sheet1").Range("D7").Offset(s - t - 2, 0).Value = ParentCode
Reformat:
ScopeSheets.Close SaveChanges:=False 'close without saving
End If
s = s + 3
t = 0
rscope = 1
ContractAmount = 0
BondCost = 0
Set BondCostLoc = Nothing
Set BaseBidLoc = Nothing
Next File 'go to the next file and repeat the process
End With
COAWorkbook.Sheets("Sheet1").Range("D5").Value = "CDI Cost"
COAWorkbook.Sheets("Sheet1").Range("I5").Value = CDICost
COAWorkbook.Sheets("Sheet1").Range("E5").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
COAWorkbook.Sheets("Sheet1").Range("C5") = "01800.0000.00.00"
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Set COAWorkbook = Nothing
Set ScopeSheets = Nothing
End Sub
Bookmarks