dynamic Pivot table range

omkar94

New Member
Joined
Apr 26, 2017
Messages
24
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:

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 Sub
Please let me know if I cant provide any further explanation or if you require any clarification.

Thank you very much!!

Omkar V
 
Last edited by a moderator:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try this...



Code:
[COLOR=#0000ff][SIZE=2]Sub [FONT=arial narrow]CreateDynaPivot(ByVal [/FONT][/SIZE][/COLOR][COLOR=#ff0000][SIZE=2][FONT=arial narrow]paramSheet [/FONT][/SIZE][/COLOR][COLOR=#0000ff][SIZE=2][FONT=arial narrow]As String, ByVal [/FONT][/SIZE][/COLOR][COLOR=#ff0000][SIZE=2][FONT=arial narrow]paramRange [/FONT][/SIZE][/COLOR][COLOR=#0000ff][SIZE=2][FONT=arial narrow]As String)

[/FONT][/SIZE][/COLOR][INDENT][COLOR=#d3d3d3][SIZE=2][FONT=arial narrow]'\\Find Last cell in the datase[/FONT][/SIZE][/COLOR][COLOR=#0000ff][SIZE=2][FONT=arial narrow]
Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Select

[/FONT][/SIZE][/COLOR][COLOR=#d3d3d3][SIZE=2][FONT=arial narrow]'\\Generate Pivot table and loop dynamic ranges[/FONT][/SIZE][/COLOR][COLOR=#0000ff][SIZE=2][FONT=arial narrow]
Do Until ActiveCell.Address = "$L$1"
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="report1!" & [/FONT][/SIZE][/COLOR][COLOR=#ff0000][SIZE=2][FONT=arial narrow]paramRange[/FONT][/SIZE][/COLOR][COLOR=#0000ff][SIZE=2][FONT=arial narrow], Version:=6).CreatePivotTable TableDestination:=[/FONT][/SIZE][/COLOR][COLOR=#ff0000][SIZE=2][FONT=arial narrow]paramSheet [/FONT][/SIZE][/COLOR][COLOR=#0000ff][SIZE=2][FONT=arial narrow]& "!R3C1", TableName:="PivotTable2", DefaultVersion:=6
Sheets([/FONT][/SIZE][/COLOR][COLOR=#ff0000][SIZE=2][FONT=arial narrow]paramSheet[/FONT][/SIZE][/COLOR][COLOR=#0000ff][SIZE=2][FONT=arial narrow]).Select

[/FONT][/SIZE][/COLOR][COLOR=#008080][SIZE=2][FONT=arial narrow]'.... the rest of your code[/FONT][/SIZE][/COLOR][COLOR=#0000ff][SIZE=2][FONT=arial narrow]
[/FONT][/SIZE][/COLOR][/INDENT]
[COLOR=#0000ff][SIZE=2][FONT=arial narrow] 
End Sub[/FONT][/SIZE][/COLOR]
 
Last edited:
Upvote 0
Hello lhartono.

Thank you very much for taking your time to answer my query, but I cannot seem to run this VBA code. when I click "F5" the macro name does not show up. could you please help me with that?

Regards,

Omkar V
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

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