Sub Bobjazz()
Selection.CurrentRegion.Select
Set wzone = Selection
nbligne = wzone.Rows.Count - 1
derligne = wzone.Row + nbligne
nbcol = wzone.Columns.Count
dercol = wzone.Column + nbcol - 1
For j = dercol To wzone.Column Step -2
Cells(1, j + 1).EntireColumn.Insert Shift:=xlToRight
Cells(wzone.Row, j).Select
Selection.Copy
Range(Cells(wzone.Row + 1, j + 1), Cells(wzone.Row + nbligne, j + 1)).Select
ActiveSheet.Paste
Next j
For i = 0 To Int(nbcol / 2) - 1
vdest = Cells(65536, wzone.Column).End(xlUp).Row + 1
Range(Cells(wzone.Row + 1, wzone.Column + (i + 1) * 3), _
Cells(wzone.Row + nbligne, wzone.Column + (i + 1) * 3 + 2)).Select
Application.CutCopyMode = False
Selection.Cut Destination:=Cells(vdest, wzone.Column)
Next i
Cells(wzone.Row, wzone.Column).Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Cells(wzone.Row, wzone.Column), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range(Cells(wzone.Row, wzone.Column + 3), Cells(wzone.Row, wzone.Column + (Int(nbcol / 2)) * 3 - 1)).EntireColumn.Select
Selection.Delete Shift:=xlLeft
Cells(wzone.Row, wzone.Column + 1).Select
ActiveCell.FormulaR1C1 = "montant"
Cells(wzone.Row, wzone.Column + 2).Select
ActiveCell.FormulaR1C1 = "type"
Selection.CurrentRegion.Select
Set wz_tcd = Selection
wsd = "'" & ActiveSheet.Name & "'!" & wz_tcd.Address(ReferenceStyle:=xlR1C1)
wdest = "'[" & ActiveWorkbook.Name & "]" & ActiveSheet.Name & "'!" & wz_tcd(1, 1).Offset(0, 4).Address(ReferenceStyle:=xlR1C1)
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=wsd).CreatePivotTable TableDestination:= _
wdest, TableName:="Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Noms")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique1").PivotFields("montant"), _
"Somme de montant", xlSum
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("type")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotSelect "", _
xlDataAndLabel, True
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
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(wz_tcd(2, 1).Offset(0, 5), wz_tcd(2, 1).Offset(0, 5 + Int(nbcol / 2))).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
Range(wz_tcd(1, 1).Offset(0, 5), wz_tcd(1, 1).Offset(0, 5 + Int(nbcol / 2) - 1)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Selection.Font.Bold = True
wz_tcd(1, 1).Offset(0, 4).ClearContents
End Sub
