VBA Pivot Table - Pivotfield Dynamic?

G

gerren01

New Member
Joined
Jan 6, 2015
Messages
2
Hi,

I have been looking for a solution to this for a while, I have found things that come close but when I try to emulate it, it fails.
I have a file that I run at any given time that has 13 months of financial account balances. Those 13 months change each month based on the current month. For instance, today is January 6th, 2015, the first month would be January 2015, and it would go thru January 2014. I need a way to write code so that the Pivot table does not label the pivotfield what it is currently, but the actual data from the report.

I need a way to change the pivotfields easily...with not too complex variable coding...
Maybe a cell reference of some sort, as the order doesnt change, the current month will always be month1 and labeled in a specific cell.
Below is the code from my macro

https://drive.google.com/file/d/0B5SBrJUlQsE-TGpDaHkyS1liYjQ/view?usp=sharing

_________________________________________
Sub PivotExample()
'
' PivotExample Macro
'


'
Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R12C22", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15
Sheets("Sheet2").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Jan"), "Count of Jan", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Dec"), "Count of Dec", xlCount
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Nov"), "Count of Nov", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Jan")
.Caption = "Sum of Jan"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Dec")
.Caption = "Sum of Dec"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Nov")
.Caption = "Sum of Nov"
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Resno")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type")
.Orientation = xlColumnField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Cono").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Facility").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Resno").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Resident Name").Subtotals _
= Array(False, False, False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable1").PivotFields("SSN").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("DOB").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Disch. Date").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Jan").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Dec").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Nov").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Oct").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sep").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Aug").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Jul").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Jun").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("May").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Apr").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Mar").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Feb").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Jan2").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Balance").Subtotals = Array _
(False, False, False, False, False, False, False, False, False, False, False, False)
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.RowGrand = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type")
.Orientation = xlRowField
.Position = 1
End With
End Sub
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
view

Here is the image of what is being pivoted...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Macro for
Replies
2
Views
65
Sanderb
S
O
  • Solved
Change the Pivot Table from another sheet
Replies
2
Views
144
Ongbey
O
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
166
Anthony47
A
D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
155
darmst24
D
C
  • Question Question
Pivot Table VBA
Replies
0
Views
206
chaddres
C

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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