changing range for pivot macro

Tonyk1051

Board Regular
Joined
Feb 1, 2021
Messages
131
Office Version
  1. 2019
Platform
  1. Windows
So i recorded a macro of me creating a pivot and thought that so long as i highlighted the entire spreadsheet and hit insert pivot, the recorded macro would work on any spreadsheet no matter how many lines, i was completely wrong... can some one modify the recorded macro below to work on any spreadsheet? (the code in blue is the error) thanks in advance

[CODE/vba] Sub Macro2()

'

' Macro2 Macro

'



'

Application.CutCopyMode = False

Sheets.Add

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

"Page1!R1C1:R45336C46", Version:=8).CreatePivotTable TableDestination:= _

"Sheet2!R3C1", TableName:="PivotTable2", DefaultVersion:=8


Sheets("Sheet2").Select

Cells(3, 1).Select

With ActiveSheet.PivotTables("PivotTable2")

.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("PivotTable2").PivotCache

.RefreshOnFileOpen = False

.MissingItemsLimit = xlMissingItemsDefault

End With

ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels

Range("B8").Select

With ActiveSheet.PivotTables("PivotTable2").PivotFields("source_time1")

.Orientation = xlRowField

.Position = 1

End With

With ActiveSheet.PivotTables("PivotTable2").PivotFields("stage2")

.Orientation = xlRowField

.Position = 2

End With

With ActiveSheet.PivotTables("PivotTable2").PivotFields("tf1")

.Orientation = xlRowField

.Position = 3

End With

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _

"PivotTable2").PivotFields("repair_id"), "Sum of repair_id", xlSum

ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _

"PivotTable2").PivotFields("repair_id"), "Sum of repair_id2", xlSum

Range("B23").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of repair_id"). _

Function = xlCount

Range("C30").Select

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of repair_id2")

.Calculation = xlPercentOfParentRow

.NumberFormat = "0.00%"

End With

Sheets("Sheet2").Select

Sheets("Sheet2").Name = "Piv"

Range("F14").Select

End Sub [/CODE]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top