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

• Ispy nagyúr

  #2862

  #2855 alexy92

  2017-06-01 15:08:35

  Ú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

  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

  

  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á.

• sztanozs veterán

  #2856

  #2855 alexy92

  2017-06-01 12:51:01

  Ú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

  

  sztanozs

  veterán

  #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

  Úbaszki, mi ez a kódkígyó...

  Semmi indent, egy csomó tök feleslges sor.

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