Dynamic Range Pivot Table

[clueless1]

Hi There,

I've looked at many posts on this subject and unfortunately I cant seem to use them to stitch together a solution, so hope this new thread doesn't annoy people.

I have a workbook with just one worksheet in it, called 'Template_data'. The number of rows in this could change, so I want to select the range dynamically and then add a Pivot Table to a new worksheet called 'Summary by Provider'. The pivot table VBA I can get from recording the marco, but its selecting the region and then telling it to use that region for the pivot table and to then insert the pivot table onto a new sheet called 'Summary by Provider'. I've tried many different methods and now feel lost, can anyone help please?

Sub DynamicRange1()'Best used when your data does not have any entirely blank rows or columns


Dim sht As Worksheet
Dim StartCell As Range


Set sht = Worksheets("Template_data")
Set StartCell = Range("A1")


'Select Range
StartCell.CurrentRegion.Select


With ActiveSheet.PivotTables("PivotTable12").PivotFields("ServiceProvider")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable12").AddDataField ActiveSheet.PivotTables( _
        "PivotTable12").PivotFields("PlanValue_WrapSipp"), _
        "Count of PlanValue_WrapSipp", xlCount
    With ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "Plan Valuation Type Group")
        .Orientation = xlPageField
        .Position = 1
    End With
  


End Sub

 

[Pranesh]

Instead of this:

StartCell.CurrentRegion.Select

Try This:

sht.UsedRange.Select

 

[clueless1]

ok, thank you, now I need to insert a new worksheet, name it and add the pivot table to it - can you help please? I'm thinking I may need to store the sht.UsedRange.Select into a Range variable to use for this part of Pivot table: "SourceData:=" ?

Sub DynamicRange1()'Best used when your data does not have any entirely blank rows or columns


Dim sht As Worksheet
Dim StartCell As Range


Set sht = Worksheets("Template_data")


sht.UsedRange.Select




With ActiveSheet.PivotTables("PivotTable12").PivotFields("ServiceProvider")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable12").AddDataField ActiveSheet.PivotTables( _
        "PivotTable12").PivotFields("PlanValue_WrapSipp"), _
        "Count of PlanValue_WrapSipp", xlCount
    With ActiveSheet.PivotTables("PivotTable12").PivotFields( _
        "Plan Valuation Type Group")
        .Orientation = xlPageField
        .Position = 1
    End With
  


End Sub

 

[Pranesh]

Try this.

Sub DynamicRange1() 'Best used when your data does not have any entirely blank rows or columns

Dim sht As Worksheet
Dim StartCell As Range

Set sht = Worksheets("Template_data")
sht.Select
sht.UsedRange.Select
Findstring = Selection.Address
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Select
ActiveSheet.Name = "Pivot"
Set sht1 = Worksheets("Pivot")
sht.Select
Dim Pt As PivotTable
Set Pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Findstring, _
    Version:=xlPivotTableVersion14).CreatePivotTable(TableDestination:="Pivot!R1C1", TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion14)
sht1.Select
End Sub

 

[clueless1]

Many thanks Pranuvins - I'll try this now

 

[clueless1]

Hi Pranuvins,

thank you but I receive an error of 'Run-time error '5': Invalid procedure call or argument'

And the line of code concerned it highlights is:

[/COLOR]Set Pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SData, Version:=xlPivotTableVersion14).CreatePivotTable(Tabledestination:="Summary by Provider!R3C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14)
[COLOR=#574123]

+ When I hover over Pt it states 'Pt = Nothing'

[/COLOR]Sub DynamicRange1()


Dim sht As Worksheet
Dim StartCell As Range
'Dim Pt As PivotTable




Set sht = Worksheets("Template_data")


sht.Select
sht.UsedRange.Select
SData = Selection.Address
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Select
ActiveSheet.Name = "Summary by Provider"


Set sht1 = Worksheets("Summary by Provider")
sht1.Select


Dim Pt As PivotTable
Set Pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SData, Version:=xlPivotTableVersion14).CreatePivotTable(Tabledestination:="Summary by Provider!R3C1", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion14)
sht1.Select


With ActiveSheet.PivotTables("PivotTable1").PivotFields("ServiceProvider")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("PlanValue_WrapSipp"), _
        "Count of PlanValue_WrapSipp", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Plan Valuation Type Group")
        .Orientation = xlPageField
        .Position = 1
    End With
  


End Sub
[COLOR=#574123]

Thanks for your help Pranuvins

 

[Pranesh]

Hi,

Replace sht1.Select to sht.Select

Set sht1 = Worksheets("Summary by Provider")
sht.Select