Hello Excel Gurus,
I am seeking some help writing this code to create multiple pivot tables from - multiple data-sets that are on one worksheet.
So my data-set is exported from a website, and it goes from Range "A1:L-infity". My Data-set is dynamic so the range changes when I export it monthly.
My Task:
Create Pivot Table from multiple data-sets on this single worksheet and export every pivot table to new worksheets.
But I don't know how to make a dynamic range pivot table. Also, each Data-set is separated by merged cells ( i used offset function for that). Take a look at the example below.
From Line 1 to 2 is data-set 1. Line 4 to 6 is data-set 2. So on and so forth.
I want to generate pivot tables for these data-sets in each separate individual sheets.
Please check out my code below ( i used recorded for most instance), and examine the font in red, where I'm having issues.
My Data-set Worksheet:
Column: A B C D E F G H I J K L
1.Title 1 Title 2 Title 3 Title 4 Title 5 Title 6 .... ..... Title12
2.123 124 45 454 4546 4646 ... ..... 420
3. Merged Cell from Range("A3:L4")
4.Title 1 Title 2 Title 3 Title 4 Title 5 TiTle 6 .... ..... Title12
5.123 124 45 454 4546 4646 ... ..... 420
6.123 124 45 454 4546 4646 ... ..... 420
7. Merged Cell from Range("A7:L7")
8.So On so forth...
My Code:
Please let me know if I cant provide any further explanation or if you require any clarification.
Thank you very much!!
Omkar V
I am seeking some help writing this code to create multiple pivot tables from - multiple data-sets that are on one worksheet.
So my data-set is exported from a website, and it goes from Range "A1:L-infity". My Data-set is dynamic so the range changes when I export it monthly.
My Task:
Create Pivot Table from multiple data-sets on this single worksheet and export every pivot table to new worksheets.
But I don't know how to make a dynamic range pivot table. Also, each Data-set is separated by merged cells ( i used offset function for that). Take a look at the example below.
From Line 1 to 2 is data-set 1. Line 4 to 6 is data-set 2. So on and so forth.
I want to generate pivot tables for these data-sets in each separate individual sheets.
Please check out my code below ( i used recorded for most instance), and examine the font in red, where I'm having issues.
My Data-set Worksheet:
Column: A B C D E F G H I J K L
1.Title 1 Title 2 Title 3 Title 4 Title 5 Title 6 .... ..... Title12
2.123 124 45 454 4546 4646 ... ..... 420
3. Merged Cell from Range("A3:L4")
4.Title 1 Title 2 Title 3 Title 4 Title 5 TiTle 6 .... ..... Title12
5.123 124 45 454 4546 4646 ... ..... 420
6.123 124 45 454 4546 4646 ... ..... 420
7. Merged Cell from Range("A7:L7")
8.So On so forth...
My Code:
Rich (BB code):
Sub Macro5()
'
' Macro5 Macro
'
'\\Find Last cell in the datase
Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Select
'\\Generate Pivot table and loop dynamic ranges
Do Until ActiveCell.Address = "$L$1"
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"report1!R816C1:R991C12", Version:=6).CreatePivotTable TableDestination:= _ '<-- Need this to be dynamic range
"Sheet1!R3C1", TableName:="PivotTable2", DefaultVersion:=6 '<-- Need this to output to different sheet each time
Sheets("Sheet1").Select '<-- Need this to output to different sheet each time
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
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Person/Description")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Net"), "Sum of Net", xlSum
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Period")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Period").AutoGroup
ActiveSheet.PivotTables("PivotTable2").PivotFields("Period").Orientation = _
xlHidden
Columns("B:M").Select
Selection.Style = "Comma"
Selection.Columns.AutoFit
Sheets("report1").Select
Selection.End(xlUp).Select
If ActiveCell.Address <> "$L$1" Then
Selection.Offset(-2, 0).Select
End If
Loop
Range("A1").Select
End SubThank you very much!!
Omkar V
Last edited by a moderator:
