Combine Dynamically Named Ranges into a Pivot Table

[greesonbrad]

Hi All,

I have multiple data sets on different sheets, all with the same column headers. I have dynamically named these sets so they change height at times. I can't seem to get a fully function Pivot Table (at least in my terms). I can get one that doesn't offer very much flexibility, but I need flexibility.

Here is a link to an example file https://www.dropbox.com/s/cf6v5aw9zvx5gj0/Example.xlsx?dl=0. I want a Pivot Table that looks and works like the Pivot Table for Just "A Data". I've tried to find a way to easily merge all my ranges, but I can't seem to figure that out either.

Ideally, I want something that doesn't use VBA or Databases, but if that's what I have to do, then that is okay.

 

[pbornemeier]

Create a dynamic named range to define each of the source ranges. This will allow a changed number of rows to be handled automatically. (you already die, but including for completeness).
Combine using the Pivot Table Wizard with ALT+D P and select Multiple Consolidation Ranges

Code for above (assuming Sheet1 and Sheet2)

Option Explicit

Sub CreateDynamicNamedRanges()

    'Create Dynamic Named range for data on Sheet1
    ActiveWorkbook.Names.Add Name:="Source1", RefersToR1C1:= _
        "=OFFSET(Sheet1!R1C1,0,0,COUNTA(Sheet1!C1),COUNTA(Sheet1!R1))"
    ActiveWorkbook.Names("Source1").Comment = ""
    
    'Create Dynamic Named range for data on Sheet2
    ActiveWorkbook.Names.Add Name:="Source2", RefersToR1C1:= _
        "=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C1),COUNTA(Sheet2!R1))"
    ActiveWorkbook.Names("Source2").Comment = ""
End Sub

Sub CreatePTFromMultipleDynamicNamedRangs()
    'Create PivotTable from multiple ranges (PT Wizard - Keyboard combo ALT+D P)
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
        Array(Array("source1", "Item1"), Array("source2", "Item2")), Version _
        :=xlPivotTableVersion14).CreatePivotTable TableDestination:="", TableName _
        :="PT_" & ActiveSheet.Name, DefaultVersion:=xlPivotTableVersion14
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables(1).DataPivotField.PivotItems( _
        "Count of Value").Position = 1
End Sub

 

[pbornemeier]

die=did in above post