OVERFLOW

Option Explicit

Public Const FilePath = "F:\DATA\FULFIL\Operations Support\Agent Performance\New Agent Performance"
Public Const FilePath2 = "F:\DATA\FULFIL\Operations Support\Agent Performance\New Agent Performance\IRT"
Public Const NO_OF_BLANK_LINES_ALLOWED As Integer = 3

Public IRT As String
Public RawData As Range, NewData As Range
Public iRow As Integer, iRow2 As Integer, BlankLineCount As Integer, NoMatch As Integer, bca As Integer
Public fs As Object



Public wcDate As Date, conDate As Date
Public midDate As String 'Not actually a date variable but to hold the value of conDate stripped of "/"
Public DayDate 'converts wcDate to an catula day i.e. Monday
Public Agent

Public WorkingFile As String

Public blnOpen As Boolean


Public Function IsOpen(WorkBookName As String) As Boolean
'Returns True if WorkBookName is open
'otherwise false
On Error GoTo NotOpen

Dim strName As String
  
  strName = Workbooks(WorkBookName).Name
  IsOpen = True
    Exit Function

NotOpen:
  IsOpen = False

End Function
Public Sub AddZero()

Dim c As Range

Range("a1").Select

For Each c In ActiveCell.CurrentRegion.Cells
  If Mid(c, 1, 1) = ":" Then
    c = 0 & c
  End If
Next

End Sub

Public Sub CSAgentsTop_Macro()
On Error GoTo Trap

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Check users are running this code with the right workbook open.
blnOpen = IsOpen("CSAgentsTop.xls")

If blnOpen = False Then
  MsgBox "Make sure you are in CSAgentsTop.xls workbook" _
  & vbNewLine & "before running the macro AllCSAgents"
  Exit Sub
End If

Call AddZero

'Now that we are in the right workbook do the stuff
wcDate = Range("B1").Value
'First find the week commencing date
conDate = wcDate + (1 - WorksheetFunction.Weekday(wcDate, 2))

midDate = Mid(conDate, 1, 2)
midDate = midDate & Mid(conDate, 4, 2)
midDate = midDate & Mid(conDate, 7, 2)
DayDate = Format(wcDate, "dddd")

Set RawData = ActiveSheet.Cells
iRow = 5
Do
iRow = iRow + 1
If RawData(iRow, 1) = "" Then
  BlankLineCount = BlankLineCount + 1
Else
  BlankLineCount = 0
  Agent = RawData(iRow, 1)
  
  Set fs = Application.FileSearch
  With fs
    .LookIn = FilePath
    .Filename = midDate & ".xls"
    .SearchSubFolders = True
    If .Execute > 0 Then
    
      For bca = 1 To fs.FoundFiles.Count
      If InStr(fs.FoundFiles(bca), "IRT") Then
        
      Else
      
          If InStr(fs.FoundFiles(bca), midDate & ".xls") <> 0 Then
            Workbooks.Open fs.FoundFiles(bca)
            WorkingFile = ActiveWorkbook.Name
            ActiveWorkbook.Worksheets(DayDate).Activate
            IRT = ActiveSheet.Range("c3").Value
            Dim Lan As Integer
            
            
' **************************************************************************
            
            Lan = 38 ' Agents if agent count increases add more agents here
            
' **************************************************************************
            
            'If IRT = "IRT" Then  'Extra
            '  Lan = 50           '
            'Else                 '
            '  Lan = 38           '
            'End If               '
            Set NewData = ActiveSheet.Cells
            iRow2 = 5
            Do
            iRow2 = iRow2 + 1
              If Trim(NewData(iRow2, 1)) = Agent Then
                If IRT <> "IRT" Then
                  Call InsertValues1
                Else
                  Call InsertValues2
                End If
              Else
                NoMatch = NoMatch + 1
              End If
              Loop Until iRow2 = Lan 'NEED TO ALTER WHAT IT LOOPS UNTIL
                                    'DEPENDING ON THE REPORT OPEN
              Workbooks(WorkingFile).Close savechanges:=True 'closes the file opened
              End If
          End If
         
      Next bca
      '------------------------------
    End If
End With
End If
Loop Until iRow = 160
'Loop Until BlankLineCount = NO_OF_BLANK_LINES_ALLOWED

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Exit_Trap:
  Exit Sub

Trap:
  MsgBox Err.Description
  Resume Exit_Trap
End Sub

Public Sub InsertValues1()
'These figures for non IRT Workbooks and top half
NewData(iRow2, 1).Offset(0, 2).Value = RawData(iRow, 1).Offset(0, 1).Value
NewData(iRow2, 1).Offset(0, 6).Value = RawData(iRow, 1).Offset(0, 8).Value
NewData(iRow2, 1).Offset(0, 8).Value = RawData(iRow, 1).Offset(0, 9).Value
NewData(iRow2, 1).Offset(0, 9).Value = RawData(iRow, 1).Offset(0, 11).Value
NewData(iRow2, 1).Offset(0, 5).Value = RawData(iRow, 1).Offset(0, 14).Value

End Sub

Public Sub InsertValues2()
'IRT Top figures. These are IRT Agents within the ALL CS Agents
NewData(iRow2, 1).Offset(0, 2).Value = RawData(iRow, 1).Offset(0, 1).Value
NewData(iRow2, 1).Offset(0, 5).Value = RawData(iRow, 1).Offset(0, 8).Value
NewData(iRow2, 1).Offset(0, 8).Value = RawData(iRow, 1).Offset(0, 9).Value
NewData(iRow2, 1).Offset(0, 9).Value = RawData(iRow, 1).Offset(0, 13).Value
NewData(iRow2, 1).Offset(0, 4).Value = RawData(iRow, 1).Offset(0, 14).Value

End Sub