When debugging my codes the red line highlight for an error. Can anyone please help me to solve the issue. Thanks
Code:
Application.ScreenUpdating = False
Application.EnableEvents = False
Call Filter_OFF
Range("'[" & ThisWorkbook.Name & "]All Info'!A4:BB60000").Clear
Application.StatusBar = "browsing through " & Server_Path & " to list all CP sheets"
Dim i As Long
Dim Recherche As ClFileSearch.ClasseFileSearch
Set Recherche = ClFileSearch.Nouvelle_Recherche
With Recherche
.FolderPath = Server_Path
.SubFolders = True
.SortBy = sort_Name
.Execute
'Excel_Files_Found = .FoundFilesCount
For i = 1 To .FoundFilesCount
full_file_name = .Files(i).strFileName
Application.StatusBar = "Reading " & full_file_name
If UCase(Right(full_file_name, 3)) <> "XLS" Then GoTo Skip_to_Next_File
'This part get the short name of the workbook....
suffix_index = Len(full_file_name)
Short_File_Name = ""
[COLOR=#ff0000] Do While Mid(full_file_name, suffix_index, 1) <> "\"[/COLOR]
Short_File_Name = Mid(full_file_name, suffix_index, 1) & Short_File_Name
suffix_index = suffix_index - 1
Loop
Application.EnableEvents = False
Workbooks.Open Filename:=full_file_name, ReadOnly:=True
'Scan throught all the sheets
'Select sheets that contain trims info
Sheets_Count = Workbooks(Short_File_Name).Sheets.Count
For Sheet_Index = 1 To Sheets_Count
Windows(Short_File_Name).Activate
Current_Open_CP_Sheet = "'[" & Short_File_Name & "]" & Sheets(Sheet_Index).Name & "'!"
'Sheet is a Critical Path
If Range(Current_Open_CP_Sheet & "A9") = "by Harry Maulloo, Floreal Knitwear Ltd" _
Then
'Remove Autofilter to allow reading of all lines
Sheets(Sheets(Sheet_Index).Name).Activate
ActiveSheet.Unprotect
ActiveSheet.AutoFilterMode = False
ActiveSheet.Unprotect
Range("A16:A65536").EntireRow.Hidden = False
If Range("'" & ThisWorkbook.Name & "'!Update_Planning_Dates_and_Calculate").Value _
Then
'Force calculation
Application.EnableEvents = False
On Error Resume Next
Application.Run "'" & Short_File_Name & "'!Update_Planning_Dates"
Application.EnableEvents = False
On Error Resume Next
Application.Run "'" & Short_File_Name & "'!Calculate"
On Error GoTo 0
End If
ThisWorkbook.Activate
'Collect Customer, S, M, P info
Customer = Range(Current_Open_CP_Sheet & "T8")
Sales = Range(Current_Open_CP_Sheet & "AM8")
Merchandiser = Range(Current_Open_CP_Sheet & "AM9")
Purchaser = Range(Current_Open_CP_Sheet & "AM10")
Last_Row = Range(Current_Open_CP_Sheet & "S10000").End(xlUp).Row
POS_Column = 4
Row_Counter_Start = 16 'This has to be set as a parameter in case of change
Trims_Item_Column = 19 'This has to be set as a parameter in case of change
For Row_Counter = Row_Counter_Start To Last_Row
POS = Range(Current_Open_CP_Sheet & "A19").Offset(Row_Counter - 19, POS_Column - 1)
POS = mod_Global.Trim_and_Format_POS(POS)
If Not (Loading_POS_Index.Exists(POS)) _
Then GoTo Move_Next_Row
Trims_Item = Range(Current_Open_CP_Sheet & "A19").Offset(Row_Counter - 19, Trims_Item_Column - 1)
If Trims_Item <> "" _
Then
'new item line line found --> counter incremented
Record_Counter = Record_Counter + 1
'Write Customer,S,M,P Fields
Range("'[" & ThisWorkbook.Name & "]All Info'!A" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, 0) = Customer
Range("'[" & ThisWorkbook.Name & "]All Info'!A" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, 1) = Sales
Range("'[" & ThisWorkbook.Name & "]All Info'!A" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, 2) = Merchandiser
Range("'[" & ThisWorkbook.Name & "]All Info'!A" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, 3) = Purchaser
'Write Trims Line
For Column_Index = 1 To 11
Range("'[" & ThisWorkbook.Name & "]All Info'!E" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, Column_Index - 1) _
= Range(Current_Open_CP_Sheet & "A16").Offset(Row_Counter - 16, Column_Index - 1)
Next Column_Index
For Column_Index = 1 To 23
Range("'[" & ThisWorkbook.Name & "]All Info'!P" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, Column_Index - 1) _
= Range(Current_Open_CP_Sheet & "S16").Offset(Row_Counter - 16, Column_Index - 1)
Next Column_Index
For Column_Index = 1 To 6
Range("'[" & ThisWorkbook.Name & "]All Info'!AM" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, Column_Index - 1) _
= Range(Current_Open_CP_Sheet & "AQ16").Offset(Row_Counter - 16, Column_Index - 1)
Next Column_Index
For Column_Index = 1 To 5
Range("'[" & ThisWorkbook.Name & "]All Info'!AS" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1, Column_Index - 1) _
= Range(Current_Open_CP_Sheet & "AY16").Offset(Row_Counter - 16, Column_Index - 1)
Next Column_Index
Range("'[" & ThisWorkbook.Name & "]All Info'!AX" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1) _
= Right(full_file_name, Len(full_file_name) - Len(Server_Path))
Range("AX" & First_Row_In_Compile_Sheet + Record_Counter - 1).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:=full_file_name, _
SubAddress:="AR" & Row_Counter
Range("'[" & ThisWorkbook.Name & "]All Info'!AX" & First_Row_In_Compile_Sheet).Offset(Record_Counter - 1).Font.Size = 8
End If
Move_Next_Row:
Next Row_Counter
End If
Next Sheet_Index
ActiveWindow.Close (False)
Skip_to_Next_File:
Next i
' Else
' MsgBox "There were no files found."
' End If
End With
Range("Standard_Line").Copy
Rows("4:" & WorksheetFunction.Max(Record_Counter + 3, 4)).PasteSpecial xlPasteFormats
Rows(WorksheetFunction.Max(Record_Counter + 3, 4) & ":65536").Clear
Application.StatusBar = "COMPLETED >>> " & Record_Counter & " Accessory Item Processed Successfully!"
Application.EnableEvents = True
