I have a Macro which prompts the user to select files to analyze then it opens each workbook propmts the user to answer some yes/no questions and select some cells. After the user responds to the message and input boxes the macro analyzes the open workbook and pastes relevant data to a masterworkbook. The macro closes the workbook and then proceed to the next file.

The problem I am having is the macro will prompt the user to select the files open the files and then stop, no error messages, nothing.

Then if I go back to the masterworkbook and start the macro again it asks me to select the files again but when it goes to open them it says the first workbook is already open. I click open anyway and it then it goes to the questions it should be asking, however when it gets to the inputbox that asks for the user to select a cell it won't allow the user to activate the openworkbook to select the cells they need to.

The code is below:


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