Macro I recorded won't run

[MarvinH]

Hi I'm a complete beginner with macros, I have 8 excel report I do every week that use the same format and tabs, I want to automate this to save me some time, I have recorded a macro in spreadsheet but I keep getting run time error 1004, can you help me understand where I am going wrong please?

 

[MarvinH]

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

 

[mrshl9898]

When the code produces an error, click debug. Let us know the error and the line that is highlighted.

At a glance you are creating and referring to a few sheets that likely don't exist.

If you create a sheet it won't always be called sheet4 for example. If you create Sheet4, rename or delete it and create another sheet it will be Sheet5.

Use this when you create a Sheet, naming it whatever you like instead of Newsheet. Then when you refer to the Sheet don't use Sheets("Newsheet") just use ws.

Sheets.Add.Name = "Newsheet"
Set ws = Sheets("Newsheet")