Hirdetés

  2. Fórumok
  3. Szoftverfejlesztés
  4. Visual Basic
Legfrissebb anyagok
PROHARDVER! témák
Mobilarena témák
IT café témák
GAMEPOD témák

Új hozzászólás Aktív témák

  • Ispy Ispy nagyúr
    #2862
    #2855 alexy92
    Új Válasz
    Új hozzászólás
    Összes hozzászólása itt Válaszok az összes hozzászólására itt Válaszok erre a hozzászólásra
    Ispy
    Ispy
    nagyúr
    #2855 alexy92
    #2855 alexy92

    Sub All()
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Radler").Activate
    Columns("K:K").Select
    Selection.Replace What:="/", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Columns("A:A").Select
    Range("L1").Select
    ActiveCell.Value = "Check if its in masterfile"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(MATCH(RC[-1],[invoices_masterfile.xlsm]main!C13,0),""Not in file"")"
    With Sheets("Radler")
    LR = Range("K" & Rows.Count).End(xlUp).Row
    Range("L1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Range("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:= _
    "Not in file"
    Range("B1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    On Error Resume Next
    Selection.Copy
    If Err.Number <> 0 Then
    CevaBelgium
    Else
    VKtrans
    End If
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Radler").Activate
    Range("B1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("H" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False

    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Radler").Activate
    Range("D1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("I" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Radler").Activate
    Range("D1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Radler").Activate
    Range("G1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, -1).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Radler").Activate
    Range("K1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[1],vendor_codes!C1:C2,2,0)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "RADLER KFT."
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Year(RC[4])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Month(RC[3])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=isoweeknum(RC[2])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[-2],MOR!C1:C2,2,0)"
    Range("K" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
    With Sheets("main")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("C1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("D1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("E1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("F1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("K1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Radler").Activate
    Rows("1:1").Activate
    Selection.AutoFilter
    Columns("L").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate


    End Sub

    Sub VKtrans()
    Selection.Clear
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("VK Transport").Activate
    Columns("A:A").Select
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Range("V1").Select
    ActiveCell.Value = "Check if its in masterfile"
    Range("V2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(MATCH(RC[-21],[invoices_masterfile.xlsm]main!C13,0),""Not in file"")"
    With Sheets("VK Transport")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("V1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Range("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$V").AutoFilter Field:=22, Criteria1:= _
    "Not in file"
    Range("B1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    On Error Resume Next
    Selection.Copy
    If Err.Number <> 0 Then
    DSVROADNV
    Else
    CevaBelgium
    End If
    Windows("invoices_masterfile.xlsm").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("VK Transport").Activate
    Range("B1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("H" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False

    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("VK Transport").Activate
    Range("L1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1, -1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("VK Transport").Activate
    Range("L1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False

    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("VK Transport").Activate

    With Sheets("VK Transport")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("O2").End(xlDown).Offset(0, 2).Select
    Range("Q2", "Q" & LR).Select
    End With
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, -1).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("VK Transport").Activate
    Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[1],vendor_codes!C1:C2,2,0)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "VK Transport"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Year(RC[4])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Month(RC[3])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=isoweeknum(RC[2])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[-2],MOR!C1:C2,2,0)"
    Range("K" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
    With Sheets("main")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("C1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("D1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("E1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("F1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("K1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("VK Transport").Activate
    Rows("1:1").Activate
    Selection.AutoFilter
    Columns("V:V").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Windows("invoices_masterfile.xlsm").Activate



    End Sub
    Sub CevaBelgium()
    Selection.Clear
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Ceva Belgium").Activate
    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.Value = "Invoice Date"
    Range("B1").Select
    ActiveCell.Value = "Service Date"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=Date(Left(RC[3],4),mid(RC[3],5,2),right(RC[3],2))"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=iferror(Date(Left(RC[20],4),mid(RC[20],5,2),right(RC[20],2)),RC[-1])"
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Range("CJ1").Select
    ActiveCell.Value = "Check if its in the masterfile"
    Range("CJ2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(MATCH(RC[-85],[invoices_masterfile.xlsm]main!C13,0),""Not in file"")"
    With Sheets("Ceva Belgium")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("CJ1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Range("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$CJ").AutoFilter Field:=88, Criteria1:= _
    "Not in file"
    Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    On Error Resume Next
    Selection.Copy
    If Err.Number <> 0 Then
    Azkar
    Else
    DSVROADNV
    End If
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Ceva Belgium").Activate
    Range("B1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Ceva Belgium").Activate

    Range("AN1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1, -1).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Ceva Belgium").Activate

    Range("AN1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Ceva Belgium").Activate
    Range("AL1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, -1).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Ceva Belgium").Activate
    Range("C1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False

    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[1],vendor_codes!C1:C2,2,0)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "CEVA FREIGHT BELGIUM N.V."
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Year(RC[4])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Month(RC[3])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=isoweeknum(RC[2])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[-2],MOR!C1:C2,2,0)"
    Range("K" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
    With Sheets("main")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("C1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("D1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("E1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("F1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("K1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Ceva Belgium").Activate
    Range("1:1").Select
    Selection.AutoFilter
    Columns("CJ:CJ").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Windows("invoices_masterfile.xlsm").Activate



    End Sub


    Sub DSVROADNV()
    Selection.Clear
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("DSV Road").Activate
    Range("S1").Select
    ActiveCell.Value = "Check if its in the masterfile"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "=Iferror(match(RC[-18],[invoices_masterfile.xlsm]main!C13,0),""Not in file"")"
    With Sheets("DSV Road")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("S1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Range("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$S").AutoFilter Field:=19, Criteria1:= _
    "Not in file"
    Range("b1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    On Error Resume Next
    Selection.Copy
    If Err.Number <> 0 Then
    DachserBE
    Else
    Azkar
    End If
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("DSV Road").Activate
    Range("b1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("H" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("DSV Road").Activate
    Range("L1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("I" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("DSV Road").Activate
    Range("L1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("DSV Road").Activate
    With Sheets("DSV Road")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("M2").End(xlDown).Offset(0, 1).Select
    Range("N2", "N" & LR).Select
    End With
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, -1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("DSV Road").Activate
    Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[1],vendor_codes!C1:C2,2,0)"
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "DSV ROAD N V"
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=YEAR(RC[4])"
    Range("D" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=month(RC[3])"
    Range("E" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=Isoweeknum(RC[3])"
    Range("F" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],MOR!C1:C2,2,0)"
    Range("K" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
    With Sheets("main")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("C1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("D1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("E1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("F1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("K1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("DSV Road").Activate
    Rows("1:1").Select
    Selection.AutoFilter
    Columns("S").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate



    End Sub

    Sub Azkar()
    Selection.Clear
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Azkar").Activate
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("P:P").Select
    Selection.TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Range("Z1").Select
    ActiveCell.Value = " Check if its in the matserfile"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(MATCH(RC[-25],[invoices_masterfile.xlsm]main!C13,0),""Not in file"")"
    With Sheets("Azkar")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("Z1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$Z").AutoFilter Field:=26, Criteria1:= _
    "Not in file"
    Range("O1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    On Error Resume Next
    Selection.Copy
    If Err.Number <> 0 Then
    DachserHU
    Else
    DachserBE
    End If
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Azkar").Activate
    Range("O1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Range("N1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1, -1).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Range("N1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Azkar").Activate
    Range("A1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Azkar").Activate
    With Sheets("Azkar")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("O2").End(xlDown).Offset(0, 1).Select
    Range("P2", "P" & LR).Select
    End With
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("K" & Rows.Count).End(xlUp).Offset(1, 1).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[1],vendor_codes!C1:C2,2,0)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "TRANSPORTES AZKAR, S.A"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Year(RC[4])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Month(RC[3])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=isoweeknum(RC[2])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[-2],MOR!C1:C2,2,0)"
    Range("K" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
    With Sheets("main")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("C1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("D1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("E1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("F1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("K1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Azkar").Activate
    Range("1:1").Select
    Selection.AutoFilter
    Columns("Z:Z").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Windows("invoices_masterfile.xlsm").Activate


    End Sub

    Sub DachserBE()
    Selection.Clear
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Dachser BE").Activate
    Range("S1").Select
    ActiveCell.Value = "Check if its in masterfile"
    Range("s2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(MATCH(RC[-2],[invoices_masterfile.xlsm]main!C13,0),""Not in file"")"
    Range("t1").Select
    ActiveCell.Value = "Date"
    Range("t2").Select
    ActiveCell.FormulaR1C1 = "=date(right(RC[-19],4),mid(RC[-19],4,2),left(RC[-19],2))"
    With Sheets("Dachser BE")
    LR = Range("Q" & Rows.Count).End(xlUp).Row
    Range("S1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("T1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Range("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$M").AutoFilter Field:=19, Criteria1:= _
    "Not in file"
    Range("T1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    On Error Resume Next
    Selection.Copy
    If Err.Number <> 0 Then
    WaberersINT
    Else
    DachserHU
    End If
    Windows("raw_invoice_riports.xlsx").Activate
    Windows("invoices_masterfile.xlsm").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("H" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("J" & Rows.Count).End(xlUp).Offset(1, -1).Select
    Windows("raw_invoice_riports.xlsx").Activate
    Selection.End(xlUp).Select
    Range("P1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("J" & Rows.Count).End(xlUp).Offset(1).Select
    Windows("raw_invoice_riports.xlsx").Activate
    Selection.End(xlUp).Select
    Range("P1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Range("F1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(0).Select
    ActiveCell.Offset(1, -1).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Range("Q1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.Offset(0, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("F" & Rows.Count).End(xlUp).Offset(1).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[1],vendor_codes!C1:C2,2,0)"
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "DACHSER TRANSPORT BELGIE"
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=YEAR(RC[4])"
    Range("D" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=month(RC[3])"
    Range("E" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=Isoweeknum(RC[3])"
    Range("F" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],MOR!C1:C2,2,0)"
    Range("K" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
    With Sheets("main")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("C1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("D1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("E1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("F1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("K1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    ActiveSheet.Range("1:1").AutoFilter
    Windows("raw_invoice_riports.xlsx").Activate
    Rows("1:1").Select
    Selection.AutoFilter
    Columns("S:T").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Windows("invoices_masterfile.xlsm").Activate



    End Sub
    Sub DachserHU()
    On Error GoTo 0
    Selection.Clear
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Dachser HU").Activate
    Range("AJ1").Select
    ActiveCell.Value = "Check if its in the masterfile"
    Range("AJ2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(MATCH(RC[-2],[invoices_masterfile.xlsm]main!C13,0),""Not in file"")"
    With Sheets("Dachser HU")
    LR = Range("AH" & Rows.Count).End(xlUp).Row
    Range("AJ1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Range("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$AJ").AutoFilter Field:=36, Criteria1:= _
    "Not in file"
    Range("V1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    On Error Resume Next
    Selection.Copy

    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Dachser HU").Activate
    Range("V1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("H" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    If Err.Number <> 0 Then
    Vege
    Else
    WaberersINT
    End If
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Dachser HU").Activate
    Range("W1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1, -1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Dachser HU").Activate
    Range("W1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Dachser HU").Activate
    Range("D1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("K" & Rows.Count).End(xlUp).Offset(1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Dachser HU").Activate
    Range("AH1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[1],vendor_codes!C1:C2,2,0)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "LIEGL & DACHSER SZALLITMANYOZASI ES"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Year(RC[4])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Month(RC[3])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=isoweeknum(RC[2])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[-2],MOR!C1:C2,2,0)"
    Range("K" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
    With Sheets("main")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("C1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("D1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("E1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("F1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("K1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Dachser HU").Activate
    Rows("1:1").Activate
    Selection.AutoFilter
    Columns("AJ").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Windows("invoices_masterfile.xlsm").Activate





    End Sub
    Sub WaberersINT()
    Selection.Clear
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Waberers INT").Activate
    Columns("A:A").Select
    Range("J1").Select
    ActiveCell.Value = "Check if its in masterfile"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(MATCH(RC[-8],[invoices_masterfile.xlsm]main!C13,0),""Not in file"")"
    With Sheets("Waberers INT")
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("J1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Range("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$J").AutoFilter Field:=10, Criteria1:= _
    "Not in file"
    Range("C1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    On Error Resume Next
    Selection.Copy
    If Err.Number <> 0 Then
    Vege
    Else
    Vege
    End If
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Waberers INT").Activate
    Range("C1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("H" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False

    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Waberers INT").Activate
    Range("F1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1, -1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Waberers INT").Activate
    Range("F1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("J" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Waberers INT").Activate
    Range("B1").Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate
    Range("M" & Rows.Count).End(xlUp).Offset(1, 0).Activate
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
    :=False, Transpose:=False
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[1],vendor_codes!C1:C2,2,0)"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "WABERERS INTERNATIONAL ZRT"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Year(RC[4])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Month(RC[3])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=isoweeknum(RC[2])"
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=vlookup(RC[-2],MOR!C1:C2,2,0)"
    Range("K" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-5]"
    With Sheets("main")
    LR = Range("H" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("B1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("C1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("D1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("E1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("F1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    Range("K1").End(xlDown).Offset(0, 0).Activate
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LR, ActiveCell.Column))
    End With
    Windows("raw_invoice_riports.xlsx").Activate
    Worksheets("Waberers INT").Activate
    Rows("1:1").Activate
    Selection.AutoFilter
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Windows("invoices_masterfile.xlsm").Activate
    Worksheets("main").Activate

    End Sub

    Sub Vege()
    Selection.Clear
    MsgBox "Siker!", vbExclamation
    End
    End Sub

    Így lenne, és a többit már tudjátok :)

    :Y

    Na, a feladat a következő: ezt az egészet mentsed el, tedd félre. Nincs az az isten, hogy tapasztalat nélkül ebbe a kóddzsungelben bármit is megtaláljál.

    Utána kezd el 0-ról, apránként, minimális kóddal. Ha megy, akkor adjál hozzá még egy részt, és így tovább. Ha nem megy, akkor állj meg és akkor térjünk vissza rá.

Új hozzászólás Aktív témák