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:
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,
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,