Új hozzászólás Aktív témák
-
alexy92
aktív tag
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
Új hozzászólás Aktív témák
- Elemlámpa, zseblámpa
- Házi barkács, gányolás, tákolás, megdöbbentő gépek!
- AMD K6-III, és minden ami RETRO - Oldschool tuning
- Trollok komolyan
- laca223: A civilizáció mint bugos operációs rendszer
- Számtech boltosok memoárjai, azaz amikor kiborulunk...
- PlayStation 5
- PlayStation 4
- Bambu Lab 3D nyomtatók
- Gumi és felni topik
- További aktív témák...
- Hibátlan 2+ Év Garancia! GIGABYTE GeForce RTX 4060 EAGLE OC 8GB
- AZONNAL KÉSZLETRŐL! AMD Ryzen 7 9800X3D 64GB 6000MHz RAM 2TB Gen4 SSD RTX 5090 32GB GDDR7 1200W
- AZONNAL KÉSZLETRŐL! Intel Core i5 14600K 64GB 6000MHz RAM 2TB Gen4 SSD RTX 5060 8GB FSP 750W
- AZONNAL KÉSZLETRŐL! Intel Core i5 14600K 32GB 6000MHz RAM 2TB Gen4 SSD RTX 5060 8GB FSP 750W
- AZONNAL KÉSZLETRŐL! Intel Core i5 14600K 32GB 6000MHz RAM 1TB Gen4 SSD RTX 5060 8GB FSP 750W
- Apple iPhone 12 Mini / 256GB / Kártyafüggetlen / 12Hó Garancia / Akku: 89%
- ÁRGARANCIA!Épített KomPhone i5 14600KF 16/32/64GB RAM RTX 5070 12GB GAMER PC termékbeszámítással
- Konzol felvásárlás!! Nintendo Switch
- ÁRGARANCIA!Épített KomPhone i5 14400F 16/32/64GB RAM RX 9060 XT 16GB GAMER PC termékbeszámítással
- Honor 90 /12/512GB / Kártyafüggetlen / 12Hó Garancia
Állásajánlatok
Cég: Laptopszaki Kft.
Város: Budapest
Cég: PCMENTOR SZERVIZ KFT.
Város: Budapest