DRAWINGS_VALIDATION:
With wsDrawings
LR1 = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim arrDriversExtract(0)
ctrDrivers = 0
For r = 2 To LR1
Debug.Print r
'store values search
NVM1 = .Cells(r, vCardCol).Value
NVM2 = .Cells(r, vDriverCol_Drawings).Value
NVMval = IIf(Trim(NVM2) = "", Trim(NVM1), Trim(NVM2))
Set vNVMFind = wsDrivers.Columns(vRegNoCol).Find(NVMval, LookIn:=xlValues, LookAt:=xlWhole)
If vNVMFind Is Nothing Then
err_type = "not found"
MsgBox (Err_in_Tomorrow1(err_type, NVMval, vRegNoCol)), vbCritical, "Error Occurred. Process Terminated."
Exit Sub
Else
Driver = vNVMFind.Offset(0, -1)
dupFound() = Filter(arrDriversExtract, Driver)
If UBound(dupFound) = -1 Then 'this is the first time we are creating a file for this driver
ReDim Preserve arrDriversExtract(ctrDrivers)
arrDriversExtract(ctrDrivers) = Driver
ctrDrivers = ctrDrivers + 1
Set wbTemplate = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "template.xlsb")
Set wsTemplate = wbTemplate.Sheets("Sheet1")
Call createDriverFile(Driver, wbTemplate, wsTemplate, wsDrawings, r)
Else 'driver already found. we are adding to the file previously created
Set wbTemplate = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & Driver & ".xlsb")
Set wsTemplate = wbTemplate.Sheets("Sheet1")
Call createDriverFile(Driver, wbTemplate, wsTemplate, wsDrawings, r)
End If
End If
Next r
End With
Function Err_in_Tomorrow1(strErrorType As String, err_NVM As String, err_offenders As Long) As String
Dim strMessage As String
Select Case strErrorType
Case "not found"
strMessage = "A driver lookup error has been found in book 'Drivers'." & vbCrLf & _
"The following information is provided by the error logger:" & vbCrLf & vbCrLf & _
"Error Description: " & "No record found" & vbCrLf & _
"NVM Registration Number in Error: " & err_NVM & vbCrLf & _
"Columns Where Errors Were Found: " & "Column(s) " & err_offenders
End Select
Err_in_Tomorrow1 = strMessage
End Function
Function createDriverFile(driverName As String, templateFile As Workbook, templateSheet As Worksheet, _
sourceSheet As Worksheet, rowTransact As Long)
Dim rowEndOfData As Long
Dim rowDriverFile As Long
With templateSheet
rowEndOfData = .Cells(.Rows.Count, 1).End(xlUp).Row
rowDriverFile = rowEndOfData + 1
End With
'copy first row of data over from "drawings"
sourceSheet.Activate
sourceSheet.Rows("1:1").Select
Selection.Copy
templateSheet.Activate
Rows("1:1").Select
ActiveSheet.Paste
'copy target row of data over from "drawings"
sourceSheet.Activate
sourceSheet.Rows(CStr(rowTransact) & ":" & CStr(rowTransact)).Select
Selection.Copy
templateSheet.Activate
Rows(CStr(rowDriverFile) & ":" & CStr(rowDriverFile)).Select
ActiveSheet.Paste
If InStr(templateFile.Name, "template.xlsb") > 0 Then 'save the file AS NEW, it is being created for the first time
templateFile.SaveAs (ThisWorkbook.Path & Application.PathSeparator & driverName & ".xlsb")
Else 'just save the file, it has been opened before
templateFile.Save
End If
ActiveWorkbook.Close
End Function