Pivot Data Selection using VBA

Nitya0808

New Member
Joined
Jul 1, 2016
Messages
42
Hi,

I have a table from Column P to Column CC

There is a Pivot table, Pivot table 3. We take a input from User for Snapshot Period.

If the input is P02, columns P to U should be selected as data source for the pivot
If the input is P03, columns V to AA should be selected as data source for the pivot
If the input is P04, columns AB to AG should be selected
P05, AH to AM
P06, AN to AS
P07, AT to AY
P08, AZ to BE
P09, BF to BK
P10, BL to BQ
P11, BR to BW
P12, BX to CC

Could you please help with the same.

Thanks,
Nitya
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Prish

Board Regular
Joined
Mar 30, 2016
Messages
91
Hi Nitya,

Not sure what your means of input are but I am assuming InputBox.

I recommend you use dynamic named ranges for each of the inputs.
Example columns P to U has a named range of "_P02"
Here's something to get you started:

Code:
Sub PTDataSource()

Dim UserInput As Variant


UserInput = InputBox("Please type in P02 / P03 / P04 etc...")


    With ActiveSheet
        .PivotTables(1).ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="_" & UserInput)
    End With


End Sub
 
Last edited:

Nitya0808

New Member
Joined
Jul 1, 2016
Messages
42
Hi,

I was trying with the below code. But i am getting error in the line where we set the PCache

Dim ws, ws1 As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim LastRow As Long
Dim LastCol As Long
Dim PRange As Range




ReportingPeriod = InputBox("Please enter the Period (between P02 - P12) to generate", "Reporting Period")
Range("CE1").Value = ReportingPeriod


Set ws = wb.Sheets("Pivots")
Set ws1 = wb.Sheets("HeadCount File")




If ReportingPeriod = "P02" Then


LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row


MsgBox (LastRow)


LastCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = ws1.Cells(2, 1).Resize(LastRow, LastCol)


Set PCache = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange).CreatePivotTable(TableDestination:=ws.Cells(43, 1), TableName:="SnapshotTable")


Set PTable = PCache.CreatePivotTable(TableDestination:=ws.Cells(43, 1), TableName:="SnapshotTable")
 

Prish

Board Regular
Joined
Mar 30, 2016
Messages
91
Have not tested, however try the following:

Code:
Sub pivot()

Dim ws, ws1 As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim LastRow As Long
Dim PRange As String


Set ws = ActiveWorkbook.Sheets("Pivots")
Set ws1 = ActiveWorkbook.Sheets("HeadCount File")


userinput:
ReportingPeriod = InputBox("Please enter the Period (between P02 - P12) to generate", "Reporting Period")


LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row


If ReportingPeriod = "P02" Then
PRange = ws1.Range("P1:U" & LastRow).Address
ElseIf ReportingPeriod = "P03" Then
PRange = ws1.Range("V1:AA" & LastRow).Address
ElseIf ReportingPeriod = "P04" Then
PRange = ws1.Range("AB1:AG" & LastRow).Address
Else
MsgBox ("Please only enter a Period between P02 - P12")
GoTo userinput
End If


Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)
Set PTable = PCache.CreatePivotTable(TableDestination:=ws.Range("A43"), TableName:="SnapshotTable")


End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,627
Messages
5,523,985
Members
409,551
Latest member
WillCaton

This Week's Hot Topics

Top