elvan28
Nouveau membre
Bonjour;
Je cherche à savoir comment je peux rectifier la macros que j'ai faite ci dessous. En effet je pars d'une base de données comptable et j'ai besoin d'extraire les dernières factures saisies. J'arrive à les sélectionner et les insérer dans mon fichier en faisant un filtre par n° > à 1007108. Cependant la prochaine extraction n'aura plus ce numéros. Je voudrais qu'il aille me chercher le dernier n° dans le fichier de destination et qu'il me fasse le filtre en fonction de ce dernier. J'espère avoir été claire.
'
[cpp] ChDir "U:\"
Workbooks.OpenText Filename:="U:\ELGI.CO", Origin:=xlMSDOS, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=-18
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-9
Selection.AutoFilter
ActiveSheet.Range("$A$1:$O$440").AutoFilter Field:=7, Criteria1:="C"
ActiveSheet.Range("$A$1:$O$440").AutoFilter Field:=3, Criteria1:="=9*"
ActiveSheet.Range("$A$1:$O$440").AutoFilter Field:=4, Criteria1:=">1007108", _
Operator:=xlAnd
Range("A2:M438").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A2").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D").Select
Selection.Cut
Columns("B:B").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-9
Columns("A:A").Select
Selection.Cut
Columns("D").Select
ActiveSheet.Paste
Columns("F:F").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("J:J").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[5]=3,""CHQ"",IF(RC[5]=12,""LCR"",IF(RC[5]=13,""LCR"",IF(RC[5]=""PRE"",""PRE"",IF(RC[5]=""LCM"",""LCM"",IF(RC[5]=""CHQ"",""CHQ"",IF(RC[5]=""VIR"",""VIR"",IF(RC[5]=""TRA"",""TRA"",IF(RC[5]=7,""LCR"","""")))))))))"
Range("F3").Select
ActiveWindow.SmallScroll Down:=-15
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F125"), Type:=xlFillDefault
Range("F2:F125").Select
ActiveWindow.SmallScroll Down:=-117
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("H:I").Select
Selection.Delete Shift:=xlToLeft
Columns("I:L").Select
Selection.Delete Shift:=xlToLeft
Range("J10").Select
ActiveWindow.SmallScroll Down:=-15
Range("A2:G125").Select
Selection.Copy
ChDir "X:\SANDRINE\ECHEANCIERS"
Workbooks.Open Filename:="X:\SANDRINE\ECHEANCIERS\2010 ech fournisseur.xls", _
Origin:=xlWindows
Range("A1146").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-111
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
ActiveWindow.SmallScroll Down:=111
Range("H11461269").Select
Range("H1269").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub [/cpp]
Merci d'avance pour votre aide
Je cherche à savoir comment je peux rectifier la macros que j'ai faite ci dessous. En effet je pars d'une base de données comptable et j'ai besoin d'extraire les dernières factures saisies. J'arrive à les sélectionner et les insérer dans mon fichier en faisant un filtre par n° > à 1007108. Cependant la prochaine extraction n'aura plus ce numéros. Je voudrais qu'il aille me chercher le dernier n° dans le fichier de destination et qu'il me fasse le filtre en fonction de ce dernier. J'espère avoir été claire.
'
[cpp] ChDir "U:\"
Workbooks.OpenText Filename:="U:\ELGI.CO", Origin:=xlMSDOS, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=-18
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=-9
Selection.AutoFilter
ActiveSheet.Range("$A$1:$O$440").AutoFilter Field:=7, Criteria1:="C"
ActiveSheet.Range("$A$1:$O$440").AutoFilter Field:=3, Criteria1:="=9*"
ActiveSheet.Range("$A$1:$O$440").AutoFilter Field:=4, Criteria1:=">1007108", _
Operator:=xlAnd
Range("A2:M438").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A2").Select
ActiveSheet.Paste
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("D").Select
Selection.Cut
Columns("B:B").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-9
Columns("A:A").Select
Selection.Cut
Columns("D").Select
ActiveSheet.Paste
Columns("F:F").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Columns("J:J").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[5]=3,""CHQ"",IF(RC[5]=12,""LCR"",IF(RC[5]=13,""LCR"",IF(RC[5]=""PRE"",""PRE"",IF(RC[5]=""LCM"",""LCM"",IF(RC[5]=""CHQ"",""CHQ"",IF(RC[5]=""VIR"",""VIR"",IF(RC[5]=""TRA"",""TRA"",IF(RC[5]=7,""LCR"","""")))))))))"
Range("F3").Select
ActiveWindow.SmallScroll Down:=-15
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F125"), Type:=xlFillDefault
Range("F2:F125").Select
ActiveWindow.SmallScroll Down:=-117
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("H:I").Select
Selection.Delete Shift:=xlToLeft
Columns("I:L").Select
Selection.Delete Shift:=xlToLeft
Range("J10").Select
ActiveWindow.SmallScroll Down:=-15
Range("A2:G125").Select
Selection.Copy
ChDir "X:\SANDRINE\ECHEANCIERS"
Workbooks.Open Filename:="X:\SANDRINE\ECHEANCIERS\2010 ech fournisseur.xls", _
Origin:=xlWindows
Range("A1146").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-111
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
ActiveWindow.SmallScroll Down:=111
Range("H11461269").Select
Range("H1269").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub [/cpp]
Merci d'avance pour votre aide