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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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:
Upvote 0
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")
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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
Back
Top