Macro to Update PowerPivot Using Cell Reference

I have been scouring the web for a few days now trying to figure this out. I found something similar in a file "filtering-a-pivot-table-based-on-the-value-of-a-cell-pivottable_filter_from_cell" but it isn't working quite right. I think my dataset from the cube is too large at several 100k rows.

Here is the setup of my workbook:

• Summary worksheet where user enters values in cells B2 - B7 and Button1 to execute the macro (in theory)
• Cube_Trend1 worksheet that is a PowerPivot connected to a network dataset of thousands of records
• Cube_Trend2 worksheet that is the same as Cube1 but with slightly different views built using the pivots

Here is what I am trying to do:

1. User enters value in cell B3 (account id) on Summary sheet
2. User enters value in cell B4 (start date) on Summary sheet
3. User enters value in cell B5 (end date) on Summary sheet
4. User clicks Button1 on Summary sheet
5. Cube_Trend1 and Cube_Trend2 Account Id is updated from B3 Summary sheet
6. Cube_Trend1 and Cube_Trend2 Calendar Date is updated from B4 and B5 from Summary sheet

I don't know if I should use a cell reference, named range, or somehow update the macro using a macro. I am just looking for the cleanest, most effective and efficient way to accomplish it.

Any help or guidance would be appreciated.

Here is the code that I get when I record the macro just trying to update the Account Id. I haven't tried the dates yet. I can then modify the 123456 in VBA and run to get the same effect but I don't want people to have to modify the VBA.

Please Login or Register  to view this content.

I have figured out the part for the AccoundID, now I am trying to figure out the date.

AccountID is this:

Please Login or Register  to view this content.

Now I am trying to get the date portion to work. I am getting an error with the below.

Please Login or Register  to view this content.