Sub BGRS()
   
'Company Code

    Application.ScreenUpdating = False
    
    ActiveWindow.ActivateNext
    Application.ScreenUpdating = False
    Sheets(1).Select
    Range("B15").Select
    Selection.copy
    Windows("BGRS PF1.xlsm").Activate
    Application.ScreenUpdating = False
    Sheets("Posting File").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Call companycode
    
    
'Reference & date
    
    ActiveWindow.ActivateNext
    Application.ScreenUpdating = False
    Sheets("Detail").Select
    Range("E4").Select
    Selection.copy
    Windows("BGRS PF1.xlsm").Activate
    Application.ScreenUpdating = False
    Range("L3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ActivateNext
    Sheets("Detail").Select
    Range("E5").Select
    Selection.copy
    Windows("BGRS PF1.xlsm").Activate
    Application.ScreenUpdating = False
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.NumberFormat = "m/d/yyyy"
    
    ActiveWindow.ActivateNext
    Application.ScreenUpdating = False

    
'check for single line
    
    If Not Range("AA13") = "" Then
        
'amount

        Range("AA12").Select
        Range(Selection, Selection.End(xlDown)).Select
        Application.CutCopyMode = False
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("P4").Select
        ActiveSheet.Paste
        Range("P3") = Application.WorksheetFunction.Sum(Selection)
        
'assignment & Text

        ActiveWindow.ActivateNext
        Application.ScreenUpdating = False
        Range("AA12").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, -17).Select
        Range(Selection, Range("J12")).Select
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("AH4").Select
        ActiveSheet.Paste
        
        ActiveWindow.ActivateNext
        Application.ScreenUpdating = False
        Range("AA12").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, -25).Select
        Range(Selection, Range("B12")).Select
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("AI4").Select
        ActiveSheet.Paste

'gl
    
        ActiveWindow.ActivateNext
        Application.ScreenUpdating = False
        Range("AA12").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, -18).Select
        Range(Selection, Range("I12")).Select
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("O4").Select
        ActiveSheet.Paste
        
'cost center
    
        ActiveWindow.ActivateNext
        Application.ScreenUpdating = False
        Range("AA12").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, -8).Select
        Range(Selection, Range("S12")).Select
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("Y4").Select
        ActiveSheet.Paste
        
'1s and 40
        
        Range("N4") = 40
        Range("P4").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, -2).Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.FillDown
        Range("N4").Select
        Selection.End(xlDown).Select
        Selection.End(xlToLeft).Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.FillDown
        Range("A1").Select

        
Call vendorVATMany

    Else
        
'amount
        
        Range("AA12").Select
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("P3").Select
        ActiveSheet.Paste
        Range("P4").Select
        ActiveSheet.Paste
        
'gl
        
        ActiveWindow.ActivateNext
        Application.ScreenUpdating = False
        Range("I12").Select
        Application.CutCopyMode = False
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("O4").Select
        ActiveSheet.Paste
        
'cost center
        
        ActiveWindow.ActivateNext
        Application.ScreenUpdating = False
        Range("S12").Select
        Application.CutCopyMode = False
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("Y4").Select
        ActiveSheet.Paste
        
'assignment & Text

        ActiveWindow.ActivateNext
        Application.ScreenUpdating = False
        Range("J12").Select
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("AH4").Select
        ActiveSheet.Paste
        
        ActiveWindow.ActivateNext
        Application.ScreenUpdating = False
        Range("B12").Select
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("AI4").Select
        ActiveSheet.Paste

        
Call vendorVATOne
        
        Range("A4") = 1
        Range("N4") = 40
        
    End If
    
    Windows("BGRS PF1.xlsm").Activate
    Sheets("Posting File").Rows(4 & ":" & Worksheets("Posting File").Rows.Count).ClearFormats
    Sheets("Posting File").Rows(3 & ":" & Worksheets("Posting File").Rows.Count).copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select

    Call copy
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Call clear
    
    MsgBox ("Done!")
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

'    ActiveWorkbook.Close
    
        
End Sub
Sub companycode()

Dim WBook As Workbook
Dim DName As Name
Dim erroVar As Variant


    Set WBook = ActiveWorkbook
    
    For Each DName In WBook.Names
    DName.Delete
    Next DName
    
    Sheets("Company Codes").Select
    Range("A1").Select
    ActiveCell.CurrentRegion.Select
    Selection.Name = "CCs"
    Sheets("Posting File").Select
    
    erroVar = Application.VLookup(ActiveCell, Range("CCs"), 2, False)
    
    If Not IsError(erroVar) Then
        Range("B3").Value = erroVar
    Else
        MsgBox "Please insert the Company Name/Code on sheet ""Company Codes"" and try again", , "Company Code Not Found!"
        
    End If
'    Range("B3").Value = WorksheetFunction.VLookup(ActiveCell, Range("CCs"), 2, False)
    
End Sub
Sub clear()

    Application.ScreenUpdating = False

    Sheets("Posting File").Select
    Sheets("Posting File").Rows(4 & ":" & Worksheets("Posting File").Rows.Count).clear
    Range("B3").clear
    Range("E3").clear
    Range("L3").clear
    Range("P3").clear
    Range("AI3").clear
    Range("AI3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(""BGRS Invoice "",R3C12,"" "",TEXT(R3C5,""mmm""))"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A3").Select
   
    Sheets("Macro BGRS PF1").Select
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    
End Sub
Sub copy()

Dim folder As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ActiveWindow.ActivateNext
    Application.ScreenUpdating = False
    ActiveWorkbook.Close
    
    Sheets("Posting File").Select
    Sheets("Posting File").copy
    folder = ThisWorkbook.Path & "\" & Range("L3") & ".xlsx"
'    ChDir ThisWorkbook.Path
    ActiveWorkbook.SaveAs Filename:=folder
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
End Sub
Sub vendorVATMany()

    Dim contador As Integer
    
    ActiveWindow.ActivateNext
    Range("O11").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -1).Select
    
    Do Until ActiveCell.Value = "Supplier VAT #"

        If ActiveCell.Value = "" Then
        
            ActiveCell.Offset(-1, 0).Select
            
        Else
            
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("AI3").Select
        ActiveSheet.Paste

        contador = contador + 1
        
        ActiveWindow.ActivateNext
        ActiveCell.Offset(-1, 0).Select
        
        End If
    
    Loop
        
    If contador > 1 Then
    
        MsgBox ("More than one VAT on this invoice!!" & vbNewLine & "Please check if multiple postings files are necessary!")
    End If
    
End Sub
Sub vendorVATOne()

    Dim contador As Integer
    
    ActiveWindow.ActivateNext
    Range("N12").Select
    
    If Not Range("N12") = "" Then
    
        Selection.copy
        Windows("BGRS PF1.xlsm").Activate
        Application.ScreenUpdating = False
        Range("AI3").Select
        ActiveSheet.Paste
        
    End If
            
End Sub