hallo ,
svp de m´aider a ameliore mon macro , il se fonctionne b1 mais il est un peut lang s´il ya pleusieur Excel ouvert
Code :
Sub sup()
'
dl = Range("A65000").End(xlUp).Row
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Select Replace:=False
Ws.Select
Ws.Name = "Lager"
Columns("A:A").Select
ActiveWorkbook.Worksheets("Lager").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Lager").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Lager").Sort
.SetRange Range("A1:A" & dl)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next Ws
With ActiveSheet
lig = .Cells.Find(What:="*VERTEILER*", LookIn:=xlValues).Row
.Rows("2:" & lig).EntireRow.Delete
End With
Range("A1") = "ARTIKEL MENGE W-LG LGM-NR/ART PLATZ DATUM LS/SER/STK-B WAQS..PLI. G V"
Range("A1:A" & dl).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(14, 1), Array(23, 1), Array(28, 1), Array(39, 2), _
Array(45, 4), Array(52, 1), Array(65, 1), Array(70, 1)), TrailingMinusNumbers:=True
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
For Each cel In Range("A1:A" & dl)
cel.Value = Replace(cel.Value, " ", "")
Next
For Each cel In Range("A1:A" & dl)
cel.Value = Replace(cel.Value, "/", "")
Next
Range("I2:I" & dl) = "=+DATE(YEAR(F2),MONTH(F2)+6,DAY(F2))<TODAY()"
ActiveWorkbook.SaveAs Filename:="C:\Temp\Start_lager.xls"
End Sub
svp de m´aider a ameliore mon macro , il se fonctionne b1 mais il est un peut lang s´il ya pleusieur Excel ouvert
Code :
Sub sup()
'
dl = Range("A65000").End(xlUp).Row
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Select Replace:=False
Ws.Select
Ws.Name = "Lager"
Columns("A:A").Select
ActiveWorkbook.Worksheets("Lager").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Lager").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Lager").Sort
.SetRange Range("A1:A" & dl)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next Ws
With ActiveSheet
lig = .Cells.Find(What:="*VERTEILER*", LookIn:=xlValues).Row
.Rows("2:" & lig).EntireRow.Delete
End With
Range("A1") = "ARTIKEL MENGE W-LG LGM-NR/ART PLATZ DATUM LS/SER/STK-B WAQS..PLI. G V"
Range("A1:A" & dl).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(14, 1), Array(23, 1), Array(28, 1), Array(39, 2), _
Array(45, 4), Array(52, 1), Array(65, 1), Array(70, 1)), TrailingMinusNumbers:=True
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
For Each cel In Range("A1:A" & dl)
cel.Value = Replace(cel.Value, " ", "")
Next
For Each cel In Range("A1:A" & dl)
cel.Value = Replace(cel.Value, "/", "")
Next
Range("I2:I" & dl) = "=+DATE(YEAR(F2),MONTH(F2)+6,DAY(F2))<TODAY()"
ActiveWorkbook.SaveAs Filename:="C:\Temp\Start_lager.xls"
End Sub