Sub main() 'This is our main function
    Dim name As String 'We'll start defining our name string, this will be the name of the sheet we'll be dealing
    name = "ND2" 'Setting our worksheet as 'ND2'
    Copy_Table (name) 'This function will copy the data from the worksheet 'name WIP' so in this case is ND2 WIP and paste in the correct fields
    Repair_data 'This function is to repair the data, because some tests in ALM use different names for each stream, for example.
    Apply_filter (name) 'This is a function to apply a filter in our sheet.

    name = "NDA" 'Setting our worksheet as 'NDA'
    Copy_Table (name) 'Copying the data from NDA WIP to our sheet
    Repair_data 'Treating our data to standardize it
    Apply_filter (name) 'Lastly filtering from A-z

    name = "Overall Graph" 'Setting our worksheet as 'Overall Graph'
    Dim linha As Long 'Defining linha as the line where we should write the next result
    linha = getFreeLine(name, "B") 'This function will get the first free line on the B column
    Call Update_Graph(name, linha) 'Now we call a function to update our graph

    Save 'We save the template with the current editions
    Save_As (getFileName(linha)) 'We save the new file using this function as name
End Sub

Public Sub Apply_filter(name As String)
    Dim wks As Worksheet
    Set wks = ThisWorkbook.Worksheets(name & " Status") 'get the NAME STATUS worksheet
    wks.Activate 'activate the worksheet (this is to prevent macro from executing in the wrong file or worksheet)


    wks.AutoFilter.Sort.SortFields.Clear 'clear filter
    wks.AutoFilter.Sort.SortFields.Add Key:= _
        Range("B1:B30000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal 'apply filter on column B using ascending order and normal sorting
    With wks.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin 'defining sort based on header and other configurations
        .Apply 'Apply the given filter
    End With
End Sub

Public Sub Save()
    ThisWorkbook.Save 'Save our workbook
    MsgBox "Os dados foram importados e a tabela foi salva com sucesso!.", vbInformation, "Save completed." 'Send a message to user informing it was finished
End Sub


Public Sub Repair_data()
    Dim LastRow As Long 'This variable is to store the last row
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row 'Well get the last row from the column A
    For i = 2 To LastRow 'Iterate through all lines
        If Range("B" & i).Value = "AMM" Or Range("B" & i).Value = "TP" Or Range("B" & i).Value = "TNP-AMM" Then 'If B = AMM or TP or TNP-AMM'
            Range("B" & i).Value = "T&P" 'change it to T&P
        ElseIf Range("B" & i).Value = "TNP-QM" Then 'If B = TNP-QM
            Range("B" & i).Value = "QM" 'change it to QM
        End If
        If Range("D" & i).Value = "Passed" Then'If the status is PASSED
            Range("E" & i).Value = "" 'Clear the ART ANALYSIS (we don't need to send our previous analysis if everything is right
        ElseIf Range("D" & i).Value = "Not Completed" Then 'If is not completed
            Range("D" & i).Value = "Failed" 'change it to failed
        End If
    Next i
End Sub

Public Sub Copy_Table(name As String)
    Dim wkb As Workbook
    Dim wks As Worksheet
    'Or_wkb = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    Set wkb = Workbooks.Open(ThisWorkbook.path & "\" & name & "_WIP.xlsx") 'Open the NAME_WIP document from the same folder as this workbook

    vLinha = 2 'Number of the first line
    Do While Range("C" & vLinha).Value <> "" 'Iterate through all the lines of the C column while they aren't blank
        vLinha = vLinha + 1
    Loop
    vLinha = vLinha - 1 'Eliminate 1 since we don't want the first line (header)
    Range("A2:D" & vLinha).Copy 'Copy everything in the rage of A2 to D:vLinha, where vLinha is the last not blank line
    Set wks = ThisWorkbook.Worksheets(name & " Status") 'Go to NAME Status sheet
    wks.Activate
    wks.Range("B2:E" & vLinha).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Use Paste special > values Only - this is to prevent ruining our formating
    wkb.Save 'Save the workbook
    wkb.Close SaveChanges:=False 'Close without saving - I don't remember why I needed to close without saving but trust me, it's necessary.
End Sub

Public Function getFreeLine(wrksName As String, field As String) As Long 'This works in a very similar logic as the function above
    Dim wks As Worksheet
    Set wks = ThisWorkbook.Worksheets(wrksName)
    wks.Activate

    vLinha = 2
    Do While Range(field & vLinha).Value <> ""
        vLinha = vLinha + 1
    Loop 'Gets the last line that isn't blank
    getFreeLine = vLinha 'returns vLinha
End Function

Public Function IsNDA(linha As Long) As Boolean 'This function is how I managed to decide wheter it's just ND2 execution or ND2-NDA
    IsNDA = False 'Start by saying it's not NDA
    If linha Mod 2 = 1 Then 'If our line is odd then it's NDA because NDA is only executed every two weeks so if the first line of NDA is odd, all of them are gonna be.
        IsNDA = True
    End If
End Function

Public Sub Save_As(fname As String)
    Dim path As String

    path = Application.ActiveWorkbook.path 'Get our workbook path
    Application.ActiveWorkbook.SaveAs Filename:=path & "\" & fname & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False 'Save it as new name (fname)
End Sub

Public Function getFileName(linha As Long) As String 'This function is to get our new file name, it always has the format 'DATE_EXECUTION Exectuion Status'
    Dim data As String 'first we get the date
    data = Format(Range("A" & linha).Value, "dd.mm.yyyy") 'Format the last line as date (with dots)
    Dim NDA As Boolean
    NDA = IsNDA(linha)
    If NDA Then
        getFileName = data & "_ND2_NDA Execution Status" 'If it's NDA execution
    Else
        getFileName = data & "_ND2 Execution Status" 'If it's not
    End If
End Function

Public Sub Update_Graph(name As String, linha As Long)
    If Range("A" & linha).Value = "" Then 'If it runs out of dates
        Range("A" & linha).Value = Date 'It will write todays date in the next line
    End If
    Dim NDA As Boolean
    NDA = IsNDA(linha)

    If NDA Then
        Range("C2").Select 'If it's NDA we get the value of ND2-NDA
    Else
        Range("D2").Select 'Else we get the value from just ND2
    End If
    Selection.Copy 'We copy the value
    Range("B" & linha).Select 'Go to line
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False 'Use paste values only (otherwise it will paste the formula)

    ActiveSheet.ChartObjects("Chart 7").Activate 'Activate our graph
    ActiveChart.ChartGroups(1).FullCategoryCollection(linha - 1).IsFiltered = False 'Insert the element corresponding line - 1 in our graph
    'This is to show the element we have just inserted in our graph

End Sub