Macro to create a Pivot Table

Hi, I'm trying to record a macro to create a pivot table in a worksheet. The worksheet is one where the pivot table will be updated on a weekly basis so therefor the first step of my recording is to delete the previous weeks pivot table in order create the current weeks pivot table in the same cells as other sheets in the workbook will vlookup the information in those cells for updated calculations.

The issue I'm having is that when I run the macro I get an error message (run time error 1004) stating that the changes cant be made for a pivot table in the selected cells.

This is the vba log for the macro, the highlighted error in the thread is this line:

"Selection.Clear"

Any help would be most appreciated, thank you.


' Keyboard Shortcut: Ctrl+i
'
Range("Q1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Clear
Range("Q3").Select
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("Table3").Select
Range("D4").Activate
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table3", Version:=6).CreatePivotTable TableDestination:= _
"SHEET NAME", TableName:="PivotTable4", DefaultVersion _
:=6
Sheets("SHEET NAME").Select
Cells(3, 17).Select
With ActiveSheet.PivotTables("PivotTable4")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable4").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable4").RepeatAllLabels xlRepeatLabels
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable4").PivotFields("NAME")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("NAME")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("NAME").CurrentPage _
= "(All)"
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Absence Type")
.PivotItems("0").Visible = False
.PivotItems(" NAME").Visible = False
End With
ActiveSheet.PivotTables("PivotTable4").PivotFields("NAME"). _
EnableMultiplePageItems = True
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("NAME"), "NAME", xlSum
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub