Question on creating a macro to insert a pivot table

[jcamacho26]

Hi,

I could use some help. I am trying to record a macro that will insert pivot table and I am stuck. I keep getting an error in the code below. Any assistance would be appreciated.


Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R77C13", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")

 

[Michael M]

You didn't select the Pivot range at the start of your code

Range("A1:M77").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R77C13", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet2").Select
Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True

 

[jcamacho26]

Thank you, Michael.

I added the range and now I am getting a "unable to run in break mode" message....The entire code is below.


StaffingModel
'

'
Range("a1:m77").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R77C13", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R4C16", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(4, 16).Select
With ActiveSheet.PivotTables("PivotTable1")
.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 = True
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = True
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = True
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = True
.CalculatedMembersInFilters = False
.RowAxisLayout xlTabularRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("New Business"), "Sum of New Business", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("P5").Select
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
Sub Macro2()
'
' Macro2 Macro
'

'
Range("A11").Select
End Sub

 

[Michael M]

You still have a mcro running !
Go up to the VBA toolbar and press the square STOP button

 

[jcamacho26]

You still have a mcro running !
Go up to the VBA toolbar and press the square STOP button

I'm getting further...thank you for your help. I am now receiving this error: Run time error

 

[jcamacho26]

.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = True
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True

 

[Michael M]

Do you already have a PivotTable at cell P16 ??
If so, Delete it !

 

[Michael M]

What is post #6 for ???

 

[jcamacho26]

Still stuck..... the bold code below is highlighted when I debug....


' StaffingModel
'

'
Range("a1:m77").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R77C13", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R4C16", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(4, 16).Select
With ActiveSheet.PivotTables("PivotTable1")
.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 = True
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False

 

[Michael M]

comment that line out and see if th code completes !