VBA - Help with update two pivot tables by multiply cell values

[Daniel_72]

Hi all,

Can someone please help me?

I am trying to write a code to run a macro an update two pivot tables in my workbok. I want the macro to take the values from 3 cells in sheet "invoice" E15:E17 and then update and refresh the pivot tables with that values. I dont want it to change when a cell value in range E15:E17 changes (ChangeByVal), instead I want to run the macro when I do the invoicing.

The workbook have a sheet "Invoice" and in cell range E15:E17 we have values for Customer, Year and month, for exampel: E15 = Customer 1, E16 = 2019, E17 = Januari

Then I have two Pivot tables in the workbook. The Pivottables are named: PInvoice1 In "Sheet 1" and PInvoice2 in "sheet 2".
The two pivottables looks the same: In B15:B17 I want to enter the values from sheet Invoice E15:E17 and then update the pivottables with that values.
IF not all 3 values in sheet invoice E15:E17 are filled or the values dont match in the pivot tables I want the macro to stop and Msg box promt: "You have to enter correct values"

Is this possible? I have tried lots of different solutions and I cant get it to work by myself ?

Can a Master help me?

Thanks in advande

//Daniel

 

[Kamolga]

Hi,

You do not need to enter values in a pivot, you just refresh it.

In your case, I would just refresh the table when I come on the sheet 1 or the sheet 2 (right click sheet name, click vieew code and paste

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

since your pivots already take values from invoice sheet, it will work. If what you want is use those vaues as filter then you would add something like

ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = sheetInvoice.range("E5").value

 

[Daniel_72]

Hi,

You do not need to enter values in a pivot, you just refresh it.

In your case, I would just refresh the table when I come on the sheet 1 or the sheet 2 (right click sheet name, click vieew code and paste

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

since your pivots already take values from invoice sheet, it will work. If what you want is use those vaues as filter then you would add something like

ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = sheetInvoice.range("E5").value

Hi,

Thanks for your answer, i have tested it and I understand the idea. The problem I have with this solution is that In the invoice sheet I have a button and a macro
that loops through the workbook and in every sheet in a specific cell that has the value print, the selected sheets saves in a PDF file. That works really good with only one customer.
My idea from the beginning was to have a call macro to put in the PDF creation macro, and from that, before the PDF files creates, filter the pivot tables with the three cell values above.

With your solution (a nice function i didn't know about, thanks ) the PDF creates with wrong information, i guess because the PDF macro dont activate all sheets so it wont update the values and refresh it.

Do you understand how i mean? Any other suggestions?

Thanks for your help and thanks in advance

//Daniel