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):
<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>
</tbody>
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):
Name | City | Jan | Feb | Mar | Apr | May | June |
John | Baltimore | 122 | 131 | 472 | 387 | 122 | 409 |
Cynthia | Boston | 104 | 469 | 494 | 214 | 110 | 233 |
Carla | Boston | 203 | 139 | 496 | 306 | 121 | 118 |
John | Baltimore | 230 | 248 | 497 | 175 | 161 | 393 |
Cynthia | Boston | 350 | 155 | 119 | 491 | 277 | 116 |
<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>
</tbody>
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,