Powerpivot and Macros - Change the reading column

scoscione

New Member
Joined
Feb 24, 2016
Messages
10
Hello,

I am looking for some help with powerpivot. I have a set of data which is organized in the format below (a sample of the data):
[TABLE="width: 309"]
<colgroup><col width="54" style="width:41pt"> <col width="68" style="width:51pt"> <col width="28" style="width:21pt"> <col width="30" style="width:23pt"> <col width="31" style="width:23pt"> <col width="29" style="width:22pt"> <col width="33" style="width:25pt"> <col width="36" style="width:27pt"> </colgroup><tbody>[TR]
[TD="width: 54"]Name[/TD]
[TD="width: 68"]City[/TD]
[TD="width: 28"]Jan[/TD]
[TD="width: 30"]Feb[/TD]
[TD="width: 31"]Mar[/TD]
[TD="width: 29"]Apr[/TD]
[TD="width: 33"]May[/TD]
[TD="width: 36"]June[/TD]
[/TR]
[TR]
[TD]John [/TD]
[TD]Baltimore[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]472[/TD]
[TD="align: right"]387[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]409[/TD]
[/TR]
[TR]
[TD]Cynthia[/TD]
[TD]Boston[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]469[/TD]
[TD="align: right"]494[/TD]
[TD="align: right"]214[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]233[/TD]
[/TR]
[TR]
[TD]Carla[/TD]
[TD]Boston[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]496[/TD]
[TD="align: right"]306[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]118[/TD]
[/TR]
[TR]
[TD]John [/TD]
[TD]Baltimore[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]248[/TD]
[TD="align: right"]497[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]393[/TD]
[/TR]
[TR]
[TD]Cynthia[/TD]
[TD]Boston[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]119[/TD]
[TD="align: right"]491[/TD]
[TD="align: right"]277[/TD]
[TD="align: right"]116[/TD]
[/TR]
</tbody>[/TABLE]


This data is lets say actuals by sales representative in each region and it relates to another budget and PY table with the same structure. I was able to consolidate all numbers using a powerpivot function and so far is being very helpful. However, every month I need to go manually and change the column of the month in which the pivot is reading. If for example is May, I have to change the month in the pivot field list and send back to users.
What I would like to actually have is an option to add control buttons and put them as a month and the user will be able to select the month and the pivot will update accordingly.

So I am trying to write this macro, but I am getting debug errors here and there reason why I kindly ask for your help. The code for example to change for the month of february is shows as below:

Code:
Sub February()
'
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Jan").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Feb"), "Sum of Feb", xlSum
End Sub

However, the code starts by hiding Jan and then add Feb collumn on the pivot. But if I dont have Jan or have any other month - the code cant start and it gives me an error.
Is there any way I can rewrite this code to make it automated and be like any month it can be switch by any month? Lets say i am in May and I want to go back and see Jan I am able to do it.

Thanks,
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

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