Pivot Data Selection using VBA

[Nitya0808]

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

 

[Prish]

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:

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

 

[Nitya0808]

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]

Have not tested, however try the following:

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