Sub Uitvoeren_naar_AA_Kranen_origineel()
Application.ScreenUpdating = False
Application.SheetsInNewWorkbook = 30
Workbooks.Add
ActiveWorkbook.SaveAs "H:\Mijn documenten\LK-Kranen " & Format(Date, "d-mm-yyyy") & ".xlsx"
Dim bestandopen
bestandopen = Dir("H:\Mijn documenten\Danny\*")
Do Until bestandopen = ""
If bestandopen = "" Then Exit Do
Workbooks.Open "H:\Mijn documenten\Danny\" & bestandopen
With ActiveWorkbook.Sheets(1)
.Range("B1:k" & .Cells.SpecialCells(xlLastCell).Row).Copy _
Workbooks("LK-Kranen " & Format(Date, "d-mm-yyyy") & ".xlsx").Sheets(1).Cells(Rows.Count, 2).End(xlUp).Offset(1)
End With
Application.DisplayAlerts = False
Workbooks(bestandopen).Close True
bestandopen = Dir
Loop
With Workbooks("LK-Kranen " & Format(Date, "d-mm-yyyy") & ".xlsx")
With .Sheets(1)
On Error Resume Next
.Range("K2:K13000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.Range("B3:K13000").Sort Key1:=Range("C2"), Order1:=xlAscending
Workbooks.Open "H:\Mijn documenten\Lijst_LK's.xls"
Workbooks("Lijst_LK's.xls").Sheets("Blad1").Range("AA1").CurrentRegion.Copy .Range("AA1")
For I = 1 To .UsedRange.Rows.Count 'rijen
For j = 1 To 11 'kolommen
.Cells(I, j) = RTrim(.Cells(I, j))
.Cells(I, 9) = Split(.Cells(I, 9))
.Cells(I, 8) = Split(.Cells(I, 8))
Next j
Next I
For Each cdt In .Columns(9).SpecialCells(2)
cdt.Value = Split(cdt.Value)
If cdt Like "##-##-####" Or cdt Like "##-#-####" Or cdt Like "#-#-####" Or cdt Like "#-##-####" Then
cdt.Value = DateValue(cdt.NumberFormat)
cdt.NumberFormat = "dd-mm-yyyy"
End If
Next cdt
.Range("B2:K" & .Cells.SpecialCells(11).Row).AutoFormat Format:=xlRangeAutoFormatClassic1
.Cells.Borders(xlInsideHorizontal).LineStyle = xlNone
.Columns.AutoFit
End With
Workbooks("Lijst_LK's.xls").Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Dim cl As Range
Dim C As Variant
Dim Rij As Long
Application.ScreenUpdating = False
sq = "Roepnaam" & "|" & "Machine" & "|" & "Omschrijving" & "|" & "Werkorder" & "|" & "Status" & "|" & "OnderhoudsType" & "|" _
& "BeginTijdstipGepland" & "|" & "EindTijdstipGepland" & "|" & "CapacitaitsgroepID" & "|" & "Werkvoorbereider" & "|"
With Sheets("Blad7")
.Range("A2") = "718.MEC"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 9, Criteria1:="718.1", Operator:=xlOr, Criteria2:="718.2"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad7").Range("A3")
End With
.AutoFilter 9, Criteria1:="718.B1", Operator:=xlOr, Criteria2:="718.B2"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad7").Range("C1500").End(xlUp).Offset(0, -2)
End With
.AutoFilter 9, Criteria1:="718.ME"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad7").Range("C2000").End(xlUp).Offset(0, -2)
Sheets("Blad7").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
End With
With Sheets("Blad8")
.Range("A2") = "718.ELE"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 9, Criteria1:="718.AB", Operator:=xlOr, Criteria2:="718.CD"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad8").Range("A3")
End With
.AutoFilter 9, Criteria1:="718.EL"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad8").Range("C1000").End(xlUp).Offset(0, -2)
Sheets("Blad8").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad9")
.Range("A2") = "718.EXT"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 9, "718.EXT"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad9").Range("A3")
Sheets("Blad9").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad10")
.Range("A2") = "726.KO"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 9, "726.KO"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad10").Range("A3")
Sheets("Blad10").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad11")
.Range("A2") = "726.EW"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 9, "726.EW"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad11").Range("A3")
Sheets("Blad11").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad12")
.Range("A2") = "718_VAST"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 9, "718_VAST"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad12").Range("A3")
Sheets("Blad12").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad15")
.Range("A2") = "Alg. Contr. van alle onderd."
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, Criteria1:="*nische controle", Operator:=xlOr, Criteria2:="=*trole van alle onderd*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad15").Range("A3")
End With
.AutoFilter 3, Criteria1:="*trole Sifa"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad15").Range("C500").End(xlUp).Offset(0, -2)
Sheets("Blad15").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad16")
.Range("A2") = "Herstellingen na controle"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, Criteria1:="=*lingen na con*", Operator:=xlOr, Criteria2:="=*sther*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad16").Range("A3")
End With
.AutoFilter 3, Criteria1:="=*ling na con*", Operator:=xlOr, Criteria2:="=*ling voor con*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad16").Range("C500").End(xlUp).Offset(0, -2)
End With
.AutoFilter 3, Criteria1:="=*ling naar con*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad16").Range("C500").End(xlUp).Offset(0, -2)
Sheets("Blad16").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
End With
With Sheets("Blad17")
.Range("A2") = "Controle tonnenkoppelingen"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, "*trole tonnenkop*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad17").Range("A3")
Sheets("Blad17").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad18")
.Range("A2") = "Stroomafnemers"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, "*van de str*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad18").Range("A3")
Sheets("Blad18").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad19")
.Range("A2") = "Veiligheidsfunties"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, "*Veiligheidsfunctie*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad19").Range("A3")
Sheets("Blad19").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad20")
.Range("A2") = "Reinigen motoren"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, Criteria1:="=*entie rei*", Operator:=xlOr, Criteria2:="=*entie fir*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad20").Range("A3")
Sheets("Blad20").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad21")
.Range("A2") = "Isolatiemetingen"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, "*na reini*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad21").Range("A3")
Sheets("Blad21").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad22")
.Range("A2") = "Smeren"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, Criteria1:="smering", Operator:=xlOr, Criteria2:="=*eren en ol*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad22").Range("A3")
End With
.AutoFilter 3, Criteria1:="=*eren alle ond*", Operator:=xlOr, Criteria2:="=*eren van alle ond*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad22").Range("C500").End(xlUp).Offset(0, -2)
End With
.AutoFilter 3, Criteria1:="=*eren : wie*", Operator:=xlOr, Criteria2:="=*eren : mot*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad22").Range("C500").End(xlUp).Offset(0, -2)
End With
.AutoFilter 3, Criteria1:="=*meren tak*", Operator:=xlOr, Criteria2:="=*eren : mot*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad22").Range("C500").End(xlUp).Offset(0, -2)
Sheets("Blad22").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad23")
.Range("A2") = "Kraanbalken"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, "*moeilijk bereikbare dele*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad23").Range("A3")
Sheets("Blad23").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad25")
.Range("A2") = "APVV"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, "APVV*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad25").Range("A3")
Sheets("Blad25").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad26")
.Range("A2") = "APVG"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, "APVG*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad26").Range("A3")
Sheets("Blad26").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad27")
.Range("A2") = "CPB"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 3, "CPB*"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad27").Range("A3")
Sheets("Blad27").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad28")
.Range("A2") = "Curatief"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 6, "CURATIEF"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad28").Range("A3")
Sheets("Blad28").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
With Sheets("Blad29")
.Range("A2") = "In voorbereiding"
.Range("B2").Resize(, 10) = Split(sq, "|")
.Range("A2:K2").Interior.ColorIndex = 37
End With
End With
With Sheets("Blad1")
With .Range("A2:K" & .Cells(Rows.Count, 11).End(xlUp).Row)
.AutoFilter 5, "In voorbereiding"
With .Offset(1).SpecialCells(xlCellTypeVisible)
.Copy Sheets("Blad29").Range("A3")
Sheets("Blad29").Columns("E:K").HorizontalAlignment = xlCenter
End With
.AutoFilter
End With
For Each cl In .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
If cl > 0 Then
Set C = .Range("AA2:AN250").Find(cl, , xlValues, xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
Do
kolom = Mid(C.Address, 3, 1)
Select Case kolom
Case "A"
With Sheets("Blad2").Cells(Rows.Count, 3).End(xlUp)
If cl <> naam Then
Rij = .Offset(1).Row
.Offset(1, -2).Resize(, 11).Interior.ColorIndex = 37
.Offset(1, -2) = cl
.Offset(1, -1).Resize(, 10) = Split(sq, "|")
End If
End With
With Sheets("Blad2")
.Cells(Rows.Count, 3).End(xlUp).Offset(1, -1).Resize(, 11) = cl.Offset(, -1).Resize(, 11).Value
.Range(.Cells(Rij, 1), .Cells(.Cells(Rows.Count, 3).End(xlUp).Row, 11)).Sort .Cells(Rij - 1, 10), , , , , , , xlYes
Sheets("Blad2").Columns("E:K").HorizontalAlignment = xlCenter
End With
Case "B"
With Sheets("Blad3").Cells(Rows.Count, 3).End(xlUp)
If cl <> naam Then
Rij = .Offset(1).Row
.Offset(1, -2).Resize(, 11).Interior.ColorIndex = 37
.Offset(1, -2) = cl
.Offset(1, -1).Resize(, 10) = Split(sq, "|")
End If
End With
With Sheets("Blad3")
.Cells(Rows.Count, 3).End(xlUp).Offset(1, -1).Resize(, 11) = cl.Offset(, -1).Resize(, 11).Value
.Range(.Cells(Rij, 1), .Cells(.Cells(Rows.Count, 3).End(xlUp).Row, 11)).Sort .Cells(Rij - 1, 10), , , , , , , xlYes
Sheets("Blad3").Columns("E:K").HorizontalAlignment = xlCenter
End With
Case "C"
With Sheets("Blad4").Cells(Rows.Count, 3).End(xlUp)
If cl <> naam Then
Rij = .Offset(1).Row
.Offset(1, -2).Resize(, 11).Interior.ColorIndex = 37
.Offset(1, -2) = cl
.Offset(1, -1).Resize(, 10) = Split(sq, "|")
End If
End With
With Sheets("Blad4")
.Cells(Rows.Count, 3).End(xlUp).Offset(1, -1).Resize(, 11) = cl.Offset(, -1).Resize(, 11).Value
.Range(.Cells(Rij, 1), .Cells(.Cells(Rows.Count, 3).End(xlUp).Row, 11)).Sort .Cells(Rij - 1, 10), , , , , , , xlYes
Sheets("Blad4").Columns("E:K").HorizontalAlignment = xlCenter
End With
Case "D"
With Sheets("Blad5").Cells(Rows.Count, 3).End(xlUp)
If cl <> naam Then
Rij = .Offset(1).Row
.Offset(1, -2).Resize(, 11).Interior.ColorIndex = 37
.Offset(1, -2) = cl
.Offset(1, -1).Resize(, 10) = Split(sq, "|")
End If
End With
With Sheets("Blad5")
.Cells(Rows.Count, 3).End(xlUp).Offset(1, -1).Resize(, 11) = cl.Offset(, -1).Resize(, 11).Value
.Range(.Cells(Rij, 1), .Cells(.Cells(Rows.Count, 3).End(xlUp).Row, 11)).Sort .Cells(Rij - 1, 10), , , , , , , xlYes
Sheets("Blad5").Columns("E:K").HorizontalAlignment = xlCenter
End With
Case "E"
With Sheets("Blad6").Cells(Rows.Count, 3).End(xlUp)
If cl <> naam Then
Rij = .Offset(1).Row
.Offset(1, -2).Resize(, 11).Interior.ColorIndex = 37
.Offset(1, -2) = cl
.Offset(1, -1).Resize(, 10) = Split(sq, "|")
End If
End With
With Sheets("Blad6")
.Cells(Rows.Count, 3).End(xlUp).Offset(1, -1).Resize(, 11) = cl.Offset(, -1).Resize(, 11).Value
.Range(.Cells(Rij, 1), .Cells(.Cells(Rows.Count, 3).End(xlUp).Row, 11)).Sort .Cells(Rij - 1, 10), , , , , , , xlYes
Sheets("Blad6").Columns("E:K").HorizontalAlignment = xlCenter
End With
Case "L"
With Sheets("Blad13").Cells(Rows.Count, 3).End(xlUp)
If cl <> naam Then
Rij = .Offset(1).Row
.Offset(1, -2).Resize(, 11).Interior.ColorIndex = 37
.Offset(1, -2) = cl
.Offset(1, -1).Resize(, 10) = Split(sq, "|")
End If
End With
With Sheets("Blad13")
.Cells(Rows.Count, 3).End(xlUp).Offset(1, -1).Resize(, 11) = cl.Offset(, -1).Resize(, 11).Value
.Range(.Cells(Rij, 1), .Cells(.Cells(Rows.Count, 3).End(xlUp).Row, 11)).Sort .Cells(Rij - 1, 10), , , , , , , xlYes
Sheets("Blad13").Columns("E:K").HorizontalAlignment = xlCenter
Sheets("Blad1").Columns("E:K").HorizontalAlignment = xlCenter
End With
Case "M"
With Sheets("Blad14").Cells(Rows.Count, 3).End(xlUp)
If cl <> naam Then
Rij = .Offset(1).Row
.Offset(1, -2).Resize(, 11).Interior.ColorIndex = 37
.Offset(1, -2) = cl
.Offset(1, -1).Resize(, 10) = Split(sq, "|")
End If
End With
With Sheets("Blad14")
.Cells(Rows.Count, 3).End(xlUp).Offset(1, -1).Resize(, 11) = cl.Offset(, -1).Resize(, 11).Value
.Range(.Cells(Rij, 1), .Cells(.Cells(Rows.Count, 3).End(xlUp).Row, 11)).Sort .Cells(Rij - 1, 10), , , , , , , xlYes
Sheets("Blad14").Columns("E:K").HorizontalAlignment = xlCenter
End With
Case "N"
With Sheets("Blad24").Cells(Rows.Count, 3).End(xlUp)
If cl <> naam Then
Rij = .Offset(1).Row
.Offset(1, -2).Resize(, 11).Interior.ColorIndex = 37
.Offset(1, -2) = cl
.Offset(1, -1).Resize(, 10) = Split(sq, "|")
End If
End With
With Sheets("Blad24")
.Cells(Rows.Count, 3).End(xlUp).Offset(1, -1).Resize(, 11) = cl.Offset(, -1).Resize(, 11).Value
.Range(.Cells(Rij, 1), .Cells(.Cells(Rows.Count, 3).End(xlUp).Row, 11)).Sort .Cells(Rij - 1, 10), , , , , , , xlYes
Sheets("Blad24").Columns("E:K").HorizontalAlignment = xlCenter
End With
End Select
Set C = .Range("AA2:AN250").FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End If
If Not cl Is Nothing Then
naam = cl
End If
Next cl
For I = .Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row).Rows.Count To 2 Step -1
If .Cells(I, 3) > 0 Then
Set C = .Range("AA2:AN250").Find(.Cells(I, 3), , xlValues, xlWhole)
If Not C Is Nothing Then
.Cells(I, 3).Offset(, -2).Resize(, 11).Delete shift:=xlUp
End If
End If
Next I
For I = .Range("I2:I" & .Cells(Rows.Count, 10).End(xlUp).Row).Rows.Count To 2 Step -1
If .Cells(I, 10) = "726.EW" Then .Cells(I, 1).Resize(, 11).Delete shift:=xlUp
Next I
End With
Dim wrksht As Worksheet
For Each wrksht In Worksheets
wrksht.Columns.AutoFit
Next wrksht
For I = 1 To 30
Sheets("Blad" & I).Name = Choose(I, "Rest", "AA-Kranen", "A-Kranen", "B en C - Kranen", "Takels", "LK45xx", "718.MEC", "718.ELE", "718.EXT", "726.KO", _
"726.EW", "718_VAST", "Dummy", "Klein gereedschap", "A.C. van alle onderdelen", "Herstellingen na controle", "Tonnenkoppelingen", "Stroomafnemers", _
"Veiligheidsfuncties", "Reinigen motoren", "Isolatiemetingen", "Smeren", "Controle kraanbalken", "Tangen", "APVV", "APVG", "CPB", "Curatief", "In voorbereiding", "Samenvatting")
Next
For x = 1 To 29
[COLOR="#FF0000"] Sheets(Choose(x, "Rest", "AA-Kranen", "A-Kranen", "B en C - Kranen", "Takels", "LK45xx", "718.MEC", "718.ELE", "718.EXT", "726.KO", _
"726.EW", "718_VAST", "Dummy", "Klein gereedschap", "A.C. van alle onderdelen", "Herstellingen na controle", "Tonnenkoppelingen", "Stroomafnemers", _
"Veiligheidsfuncties", "Reinigen motoren", "Isolatiemetingen", "Smeren", "Controle kraanbalken", "Tangen", "APVV", "APVG", "CPB", "Curatief", "In voorbereiding")).Range("A2").AutoFilter[/COLOR] Next
With Workbooks("LK-Kranen " & Format(Date, "d-mm-yyyy") & ".xlsx")
Workbooks.Open "H:\Mijn documenten\Lijst_LK's2.xls"
With .Sheets("Samenvatting")
Workbooks("Lijst_LK's2.xls").Sheets(1).Range("B2:F35").Copy .Range("B2")
.Columns("A:F").ColumnWidth = 27.86
End With
Workbooks("Lijst_LK's2.xls").Close False
Windows("LK-Kranen " & Format(Date, "d-mm-yyyy") & ".xlsx").Activate
With Application
.ScreenUpdating = True
.SheetsInNewWorkbook = 3
.Save
' .Close True
End With
End With
End Sub