sobeitjedi
Board Regular
- Joined
- Mar 13, 2006
- Messages
- 235
- Office Version
- 365
Hi.
I have created a macro to create a pivot table. It all works apart from one thing. The sheet I created the macro with has two columns (in a & b) and 426 rows. How to I change my macro so that it will use all the rows that are populated in columns a & b as it won't always be 426 rows (could be more, could be less).
Here's my code:
I have created a macro to create a pivot table. It all works apart from one thing. The sheet I created the macro with has two columns (in a & b) and 426 rows. How to I change my macro so that it will use all the rows that are populated in columns a & b as it won't always be 426 rows (could be more, could be less).
Here's my code:
Sub Macro5()
'
' Macro5 Macro
'
'
Range("A1:B426").Select
Range("B9").Activate
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R426C2", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R1C4", TableName:="PivotTable7", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(1, 4).Select
With ActiveSheet.PivotTables("PivotTable7")
.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("PivotTable7").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable7").RepeatAllLabels xlRepeatLabels
ActiveSheet.PivotTables("PivotTable7").AddDataField ActiveSheet.PivotTables( _
"PivotTable7").PivotFields("Description#1"), "Count of Description#1", xlCount
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Description#1")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable7").PivotFields("Date").AutoGroup
ActiveSheet.PivotTables("PivotTable7").PivotFields("Months").Orientation = _
xlHidden
Range("D13").Select
End Sub