To create a pivot table with dynamic range of values

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
Hi everyone,
Here i am trying to create a pivot table from where i have a table named "Live Employees" and from that table i am trying to create a pivot table where i would not be knowing the range of that sheet.I have tried this code but its throwing me some error , can anyone help me out with this ...
Code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Live Employees!" & Sheets("Live Employees").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1), Version:=xlPivotTableVersion6).CreatePivotTable _
    TableDestination:="Salary!R3C1", TableName:="PivotTable6", DefaultVersion:=xlPivotTableVersion6


Thank you in advance
 

Some videos you may like

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.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

Have you simply tried Range("A1").UsedRange ...

Hope this will help
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
Hi Everyone,
I have done with creating pivot table with dynamic values, but while creating when it goes to ForEach loop it creates a column named "Values" which i need that to be ignored , cacn anyone suggest me how can i ignore that.
Below is my code for creating PivotTable Dynamically

Code:
Worksheets("Live Employees").Activate
    Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=1, SourceData:=Range("A3").CurrentRegion.Address)
    Worksheets.Add
    ActiveSheet.Name = "Salary"
    ActiveWindow.DisplayGridlines = False
    Set pt = ActiveSheet.PivotTables.Add(PivotCache:=PCache, TableDestination:=Range("A3"), TableName:="PivotTable1")
    
    Sheets("Salary").Select
    Cells(3, 1).Select


    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost Center")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    With ActiveSheet.PivotTables("PivotTable1")
        For Each wField In .PivotFields
            Select Case wField.Name
                Case "Cost Center", "Employee ID", "Employee Name", "Date of Joining", "Grade", "Designation", "States", "Branch", "Division", "Department", "Base Attendance", "EligibleAttendance", "LOP", "Previous Period LOP", "Previous Period LOP Reversal", "ChildrenHostelAllowance", "Total Earning", "VoluntaryProvidentFund", "Total Deduction"
                Case Else: .AddDataField .PivotFields(wField.Name), "Sum of " & wField.Name, xlSum
            End Select
        Next
    End With

Thank you in Advance
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

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
Top