- Mindenki AI-t akar, már 2025-re is eladták a HBM chipeket
- Telekom otthoni szolgáltatások (TV, internet, telefon)
- Súlyos adatvédelmi botrányba kerülhet a ChatGPT az EU-ban
- Proxmox VE
- Kapnak egy rakás reklámot a Roblox játékosai
- ArchiCAD és Artlantis topik
- Mikrotik routerek
- YouTube
- WLAN, WiFi, vezeték nélküli hálózat
- Linux kezdőknek
Új hozzászólás Aktív témák
-
alexy92
aktív tag
Sziasztok,
Egy olyan makrón dolgozom, ami több hasonló műveletet végez el, több sheetről. A kérdésem az lenne, hogy van-e olyan parancs ami közé be tudom illeszteni az egyes részeket, hogy ha azon belűl bármi hibára fut, akkor azt a részt ne futassa le, hanem egyből a következő hasábra menjen?
Előre is köszönöm!
-
alexy92
aktív tag
válasz Bobrooney #2812 üzenetére
Köszi nézegettem, de nem nagyon érzem úgy, hogy az én megoldásom lenne.
Csináltam egy képet, hogy ábrázoljam mi is az én bajom:
És kb most 30-40 ilyen blokkról beszélek. Nem debugot keresek, hanem egyszerűen az általam választét részt hagyja el.
Köszönöm!
[ Szerkesztve ]
-
alexy92
aktív tag
válasz Delila_1 #2817 üzenetére
Szia,
Most sikerült ténylegesen foglalkoznom vele, és a helyzet az, hogy szépen tovább lép a suboknál, de sajnos akkor is amikor nem dobna fel hibát. Mit ronthattam el ?
On Error Resume Next
Selection.Copy <- itt szokott kijönni az 1004-es hiba, mert sok a kijelölt cella
If Err.Number <> 0 Then kövi utáni sub Else Kövi subDe itt mindig a "kövi sub" jön ki.
Előre is köszönöm,
Viktor -
alexy92
aktív tag
-
alexy92
aktív tag
válasz Delila_1 #2846 üzenetére
A probléma az, hogy,
Sub1
művelet
művelet
művelet
On Error Resume Next
Selection.Copy <- itt szokott kijönni az 1004-es hiba, mert sok a kijelölt cella
If Err.Number <> 0 Then sub3 Else sub2
művelet
művelet
művelet
endsub2
On error goto 0
művelet
műveletHa én a kövi sub elején nullázom le, akkor a kövi subot folytatja, az if err.number utáni részt elhagyja, ha hibára fut, ha nem. Értelemszerűen, ha hibára fut, akkor jól teszi, de nekem jelenleg hiba nélkül is átugorja.
Köszi!
-
alexy92
aktív tag
Szia,
Sajnos nem vagyok még expert, így tudnál segíteni a változóban? Illetve a változó minden sub elejére kell?
Azt látom, ott ahol hibára futok, oda kell majd berakjam az on error goto VÁLTOZÓ. ÉS a változóban lesz majd az iferror, a subok elején meg az Iserror-t false-á teszem.Köszi!
-
-
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
[ Szerkesztve ]
Új hozzászólás Aktív témák
- ThinkPad (NEM IdeaPad)
- Diablo IV
- antikomcsi: Való Világ: A piszkos 12 - VV12 - Való Világ 12
- Linux felhasználók OFF topikja
- A fociról könnyedén, egy baráti társaságban
- Dell notebook topic
- (nem csak) AMD FX / Ryzen tulajok OFF topikja
- Eredeti játékok OFF topik
- Modern monitorokra köthető 3dfx Voodoo kártya a fészerből
- Rezsicsökkentés, spórolás (fűtés, szigetelés, stb.)
- További aktív témák...
- Újszerű - ASROCK B450 Fatal1ty Gaming K4 AMD AM4 alaplap + Windows 10/11 HOME digitális licensz
- Újszerű - ASROCK B450 Pro4 AMD AM4 dobozos alaplap
- HP Elitedesk 800 G4 DM I5-8500T 16GB 256GB SSD (1 USB sérült, de működik)
- Dell 7060 Micro I5-8500T 8GB 500 GB SSD WIFI
- DELL LATITUDE 7390 I5-8250U/8GB/256GB SSD/1920X1080