vba code to reference value in Pivot table

[Sintek]

Hi All

I have a Pivot table that I need to reference to populate a TextBox. The TextBox value must become 6795 taken from PivotTable. Very new to this so please help.

Please Login or Register  to view this content.

[Sintek]

Nevermind...Guess it would be easier just to reference the cell in the sheet

Please Login or Register  to view this content.

[isskint]

What if you have more or less rows in the pivottable? the total may not be at B11? Have a look at the Getpivotdata() function.

[Sintek]

Hi All

I spoke to soon. How about allowing me to populate my textbox with today total or allowing a choice of date....Any ideas. I have attached my file

[Sintek]

Hi isskint

I am lost...It's greek to me...Been on web and just can't understand.

[isskint]

have a look at this link https://support.office.com/en-gb/art...e-3af55960761f

So if you have a field called Jedi then Getpivotdata("Jedi",A1) will return the sum of Jedi (where A1 is in the pivot table)

[Sintek]

I'm sorry isskint

Maybe I'm having a blonde moment...I just can't comprehend.

Please Login or Register  to view this content.

[isskint]

Where is the textbox? on the sheet or on a form?

The way i would do this is to have the Getpivotdata formula in a cell and reference that cell for textbox 5.
If you want to do it through VBA then the correct use is something like;

Please Login or Register  to view this content.

so

Please Login or Register  to view this content.

would return todays data

[Sintek]

The Textbox is on a Userform. i am busy creating a POS system. I need to be able to click on comand button to generate value in textbox i.e. Daily sales, Monthly sales and grand total.

i have attached an updated file.

[isskint]

OK, so the code you would want for each of the buttons is as below;

Please Login or Register  to view this content.

[Sintek]

Awesome, thanks so much. Is there a way to change the following line of code to get a monthly total i.e. Current month to date

Please Login or Register  to view this content.

[Sintek]

Hi All

Can anyone perhaps assist with above code.

[isskint]

I do not think you can with GetPivotData.

If you always SPECIFICALLY want current month to date you could calculate the date at end of last month then use GetPivotData to get the total on that date THEN deduct that from pvtTable.GetPivotData("Sum of Total", "Date", Date).

The better approach would be a separate pivot where the dates are grouped by month OR add an extra column to the table giving you month/year and use that in an extra pivot table.

[Sintek]

Hi isskint

Thanks for feedback. Think I will use the option of creating another Pivot table.
By the way...The below code for daily total all of a sudden throws an runtime 1004 error Application defined or Object defined error and I have no idea why.

Please Login or Register  to view this content.

[Sintek]

Not to worry... Sorted all out including Monthly....Thanks isskint.
My Headings of Pivot Table were incorrect.