I have the below code but when I get to the line "Range("B5").Select" I get an error message. Does anybody have an idea why ?
Sub Macrosousrach()
ActiveWorkbook.Activate
Sheets(1).Name = "Data"
Range("A:C,F:G,I:P,R:V").Select
Range("R1").Activate
Selection.Delete Shift:=xlToLeft
Range("D2").Select
Columns("D:D").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Style = "Comma"
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = _
"_-* #,##0.0 _€_-;-* #,##0.0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.NumberFormat = "_-* #,##0 _€_-;-* #,##0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "Montant S/R"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=+IF((RC[-1]=""R""),(RC[1]*-1),RC[1])"
Range("D2").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("d2").AutoFill Destination:=Range("d2:d" & LR)
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R30000C6").CreatePivotTable TableDestination:="", _
TableName:="Tableau croisé dynamique1", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Code Apporteur")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique1").PivotFields("Montant S/R"), _
"Somme de Montant S/R", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Selection.Sort Key1:="R5C2:R51C2", Order1:=xlAscending, Type:= _
xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
Dim LR2 As Long
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & LR2).Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
End Sub
Sub Macrosousrach()
ActiveWorkbook.Activate
Sheets(1).Name = "Data"
Range("A:C,F:G,I:P,R:V").Select
Range("R1").Activate
Selection.Delete Shift:=xlToLeft
Range("D2").Select
Columns("D:D").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Style = "Comma"
Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = _
"_-* #,##0.0 _€_-;-* #,##0.0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.NumberFormat = "_-* #,##0 _€_-;-* #,##0 _€_-;_-* ""-""?? _€_-;_-@_-"
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "Montant S/R"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=+IF((RC[-1]=""R""),(RC[1]*-1),RC[1])"
Range("D2").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("d2").AutoFill Destination:=Range("d2:d" & LR)
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R30000C6").CreatePivotTable TableDestination:="", _
TableName:="Tableau croisé dynamique1", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields( _
"Code Apporteur")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique1").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique1").PivotFields("Montant S/R"), _
"Somme de Montant S/R", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Selection.Sort Key1:="R5C2:R51C2", Order1:=xlAscending, Type:= _
xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom
Dim LR2 As Long
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & LR2).Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
End Sub