Run-time error '1004'; Application-defined or object-defined error on Pivot Table VBA

Hey Guys,

I have a file that pulls data sets from SQL one Data set runs the sub ‘Filter’ fine to filter the pivot, but when I pull in the second data set from the same Table in SQL Database but a different filter on ‘Plant_sys’ (SQL) it does not work.

Pivot table filter Sub, the Value in R1 is updated by a double click

Please Login or Register  to view this content.

I tried manually recording myself filtering the pivot table, but when I try to run the recording I get the Run-time error '1004'

Please Login or Register  to view this content.

I have Also attached sample Data from both SQL Queries Tab labeled ‘Issue Data’ is the one where the pivot Sub ‘Filter’ is not working nor the Manually recorded Sub ‘Macro3’
Tab labeled ‘Working Data’ is a small sample of the working table

I have been trying to figure the issue out for hours, any and all help is appreciated.


Pivot table where the Sub should update the filter

Capture.PNG


I hope i am not adding too much to the post, but when i load the data set where it does not work i get the following when running the filter Sub
Run-time error '424':
Object required

on this line on the Filter Sub

Please Login or Register  to view this content.

Sorry I had difficulties attaching the file, I can add more fields/rows if needed I was trying to reduce the .xlsm file size, but had to zip

Hey Rory,

Thanks for having a look, i am having some IT issues today i will get a file with the code attached added ASAP.

The Target is declared via the double click see below

Please Login or Register  to view this content.

this would in turn trigger the following as i have it on the DATA Hold worksheet code:

Please Login or Register  to view this content.

Sorry Just realized I attached the wrong sub in the initial post, code still the same

Hey,

Finally managed to get the file attached, I forgot I had a hidden sheet with over 100k lines of data!!!

Thanks again for any and all support

Originally Posted by CheeseSandwich

The value you are filtering by must exist in the pivots data or else you will get this error.

The number provided in the code '4511728042' does not exist
The number '0' in cell R2 of the 'Data Hold' tab also does not exist

If you run your recorded macro with a value that does exist it works fine:

Please Login or Register  to view this content.

Hey CheeseSanwitch,

Thanks for having a look, so with the recorded SUB I was trying to show that even if I recorded myself doing it manually and then went to call that sub it would not work, it was with another data set I tested that WO if you do it with ORDER NUMBER 4511884007 however it will still not work!

But the way I want it to work is by Double-clicking on one of the order Numbers in range H16:H26 it will then call on the Sub Worksheet_BeforeDoubleClick however if you try it with Cell H17 for eg it will not work as it is from Plant 0002 but if you change the PLANT_SYS Filter to 0003 and try any of the Order Numbers it will work!!

This is the bit I am completely confused with!

Please Login or Register  to view this content.

Hey,

When i put that in the code for Sheet6, i get the following error?

Attachment 785299

Originally Posted by CheeseSandwich

I can't see that attchment. Have you looked at the file I attached?

Hey,

Sorry i did not realize you shared an updated file so that works if I double click on the Order Number in Sheet2 (summary), but it is Sheet6(Summary1) I want to link so if they double click on range H16:H26 on tab Summary1 it will update the Pivot table3 on Tab Summary

My Bad I was not super clear on what tab I was referring to on the range

Originally Posted by CheeseSandwich

try putting the below into the code window of 'Summary1' tab

Please Login or Register  to view this content.

You can restrict it to only work with specific cells if you wish, as below:

Please Login or Register  to view this content.

Hey,

This is giving me the same issue as i had this works great when you select plant_sys 0003 but if you pick Plant_sys 0002 you now get the following

Attachment 785313

Hey,

I have managed to get part of the way there with the following, but it only puts the Order Number in the search field and not return any results!

Please Login or Register  to view this content.

Originally Posted by CheeseSandwich

Have a look at the attached.

Hey no joy i am afraid!! When you select 0002 on the slicer and double click an Order Number you get the following

Attachment 785322