I've done something wrong but not sure what, here is my full code
Sub Macro1()
'
' Macro1 Macro
'
'Sub AddPivot()
Dim ws As Worksheet
Sheets.Add.Name = "Newsheet"
Set ws = Sheets("Newsheet")
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1" , Version:=6).CreatePivotTable TableDestination:= _ ws.Range("A3"),
TableName:=("PivotTable3"), DefaultVersion:=6
Sheets("NewSheet").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Complaint Reference Number"), _
"Count of Complaint Reference Number", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Download Transaction Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Download Transaction Date") _
.AutoGroup
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Transaction")
.Orientation = xlRowField
.Position = 1
End With
Range("B4").Select
Selection.Group Start:=True, End:=True, By:=7, Periods:=Array(False, _
False, False, True, False, False, False)
Columns("B:G").Select
Selection.ColumnWidth = 10
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I12").Select
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Flow"
Range("H7").Select
Selection.ShowDetail = True
Sheets("Flow").Select
Sheets("Flow").Move Before:=Sheets(1)
Sheets("Sheet5").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table2", Version:=6).CreatePivotTable TableDestination:="Sheet6!R3C1", _
TableName:="PivotTable4", DefaultVersion:=6
Sheets("Sheet6").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Complaint Reference Number"), _
"Count of Complaint Reference Number", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Download Transaction Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("Download Transaction Date") _
.AutoGroup
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Transaction")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Corresponding Business Area 2")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("C User Group")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("Transaction"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable4").PivotFields("Transaction").CurrentPage _
= "Trans In"
Range("B4").Select
Selection.Group Start:=True, End:=True, By:=7, Periods:=Array(False, _
False, False, True, False, False, False)
Columns("B:G").Select
Selection.ColumnWidth = 10
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A5").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Corresponding Business Area 2").AutoSort xlDescending, _
"Count of Complaint Reference Number"
Range("A6").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("C User Group").AutoSort _
xlDescending, "Count of Complaint Reference Number"
ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"Corresponding Business Area 2").ShowDetail = False
With ActiveWindow
.SplitColumn = 1
.SplitRow = 0
End With
ActiveWindow.FreezePanes = True
Range("K7").Select
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Trans in by Queue"
Range("C24").Select
Sheets("Flow").Select
Range("H5").Select
Selection.ShowDetail = True
Sheets("Sheet7").Select
Sheets("Sheet7").Move Before:=Sheets(4)
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table3", Version:=6).CreatePivotTable TableDestination:="Sheet8!R3C1", _
TableName:="PivotTable5", DefaultVersion:=6
Sheets("Sheet8").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
"PivotTable5").PivotFields("Complaint Reference Number"), _
"Count of Complaint Reference Number", xlCount
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Transaction")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable5").PivotFields( _
"Download Transaction Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable5").PivotFields("Download Transaction Date") _
.AutoGroup
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Method of Receipt")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable5").PivotFields("Transaction"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable5").PivotFields("Transaction").CurrentPage _
= "Log"
With ActiveSheet.PivotTables("PivotTable5").PivotFields("PCR4")
.Orientation = xlRowField
.Position = 2
End With
Range("B4").Select
Selection.Group Start:=True, End:=True, By:=7, Periods:=Array(False, _
False, False, True, False, False, False)
Columns("B:G").Select
Selection.ColumnWidth = 10
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A5").Select
ActiveSheet.PivotTables("PivotTable5").PivotFields("Method of Receipt"). _
AutoSort xlDescending, "Count of Complaint Reference Number"
Range("A6").Select
ActiveSheet.PivotTables("PivotTable5").PivotFields("PCR4").AutoSort _
xlDescending, "Count of Complaint Reference Number"
ActiveSheet.PivotTables("PivotTable5").PivotFields("Method of Receipt"). _
ShowDetail = False
With ActiveWindow
.SplitColumn = 1
.SplitRow = 0
End With
ActiveWindow.FreezePanes = True
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "Logged by MOR"
Range("D26").Select
Sheets("Flow").Select
End Sub