Sub Macro2()
'
' Macro2 Macro
' Macro enregistrée le 31/05/2011 par *****
'
'
Sheets("Base coda").Select
[COLOR="red"]Columns("J:J").Select
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False[/COLOR]
Range("K1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "Pièce"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[3],8)"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K10000")
Range("K2:K1980").Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("STT diva").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Base diva'!R1C1:R10000C9").CreatePivotTable TableDestination:= _
"'[ECARTS 2.0.xls]STT diva'!R3C1", TableName:="Tableau croisé dynamique2", _
DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
Sheets("STT coda").Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Base coda'!R1C6:R10000C28").CreatePivotTable TableDestination:= _
"'[ECARTS 2.0.xls]STT coda'!R3C1", TableName:="Tableau croisé dynamique3", _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields("Pièce")
.Orientation = xlRowField
.Position = 1
End With
[COLOR="yellow"]ActiveSheet.PivotTables("Tableau croisé dynamique3").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique3").PivotFields("Mt EUR"), _
"Nombre de Mt EUR", xlCount[/COLOR]
Sheets("STT diva").Select
With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
"N° pièce")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique2").PivotFields( _
"Montant HT de la ligne de pièce"), "Nombre de Montant HT de la ligne de pièce" _
, xlCount
Range("B8").Select
ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
"Nombre de Montant HT de la ligne de pièce").Function = xlSum
Sheets("STT coda").Select
Range("B12").Select
ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
"Nombre de Mt EUR").Function = xlSum
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("STT diva").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:3").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("STT coda").Select
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Range("A10").Select
Sheets("Base diva").Select
Columns("B:B").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("Base coda").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("STT diva").Select
Columns("A:A").Select
Selection.NumberFormat = "@"
Selection.ColumnWidth = 13.71
Columns("A:B").Select
Selection.Copy
Sheets("Diva-Coda").Select
Columns("A:B").Select
ActiveSheet.Paste
Sheets("STT coda").Select
Columns("A:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Coda-Diva").Select
Columns("A:B").Select
ActiveSheet.Paste
Sheets("Coda-Diva").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C2:C10000")
Range("C2:C961").Select
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D10000")
Range("D2:D961").Select
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Base coda'!R2C1:R20000C27,10,FALSE)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E10000")
Range("E2:E961").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Base coda'!R2C1:R20000C27,2,FALSE)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F10000")
Range("F2:F961").Select
Range("D10").Select
Sheets("STT diva").Select
Selection.Copy
Sheets("Diva-Coda").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C2:C10000")
Range("C2:C934").Select
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D10000")
Range("D2:D934").Select
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E10000")
Range("E2:E934").Select
Range("E2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Base diva'!R2C1:R20000C10,4,FALSE)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E10000")
Range("E2:E934").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Base diva'!R2C1:R20000C10,2,FALSE)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F10000")
Range("F2:F934").Select
Range("H13").Select
Columns("B:D").Select
Selection.NumberFormat = "#,##0.0"
Sheets("Coda-Diva").Select
Columns("B:D").Select
Selection.NumberFormat = "#,##0.0"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Mt Coda"
Range("B2").Select
Sheets("Diva-Coda").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "Mt Diva"
Range("B2").Select
Selection.AutoFilter Field:=3, Criteria1:="=0.0", Operator:=xlAnd
Range("A2:F1500").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("RESULTAT").Select
Range("K3").Select
ActiveSheet.Paste
Sheets("RESULTAT").Select
ActiveWindow.SmallScroll Down:=-9
ActiveSheet.Paste
Sheets("Coda-Diva").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RESULTAT").Select
ActiveSheet.Paste
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Diva-Coda").Select
Selection.AutoFilter Field:=3, Criteria1:="=0.0", Operator:=xlAnd
Range("A25:C25").Select
Sheets("Diva-Coda").Select
Range("A1:F1000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RESULTAT").Select
Range("D2").Select
ActiveSheet.Paste
Sheets("Coda-Diva").Select
Selection.AutoFilter Field:=3, Criteria1:="<>0.0", Operator:=xlAnd
Selection.AutoFilter Field:=4, Criteria1:=">0.1", Operator:=xlOr, _
Criteria2:="<-0.1"
Range("A1:E10001").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RESULTAT").Select
Range("H24").Select
Range("R3").Select
Sheets("Diva-Coda").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Coda-Diva").Select
Range("A1:F10001").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RESULTAT").Select
Range("R2:W2").Select
ActiveSheet.Paste
Range("Y1:Y9").Select
Range("Y9").Activate
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
Range("R1:W2").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 15
Columns("R:W").Select
Range("R2").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("K1:P2").Select
Selection.Interior.ColorIndex = 15
Selection.Font.Bold = False
Selection.Font.Bold = True
Columns("K:P").Select
Range("K2").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("D1:I2").Select
Selection.Interior.ColorIndex = 15
Selection.Font.Bold = False
Selection.Font.Bold = True
Columns("D:I").Select
Range("D2").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub