+ Reply to Thread
Results 1 to 20 of 20

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

  1. #1
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

    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.

  2. #2
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    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
    Attached Files Attached Files

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,245

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

    There is no code in the workbook you attached.

    Where is Target declared in your code and how is it instantiated?
    Rory

  4. #4
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    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

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,245

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

    So what's the error and on which line in that code? It won't be the 424 error you mentioned earlier.

  6. #6
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    Hey,

    Sorry for the delay i appreciate your help!!

    So if I take out the On Error I get the Run-time error '1004'; the error is on the following line

    Please Login or Register  to view this content.
    In the attached file if you go onto the Tab 'Summary 1' and in the plant_sys Slicer select '0003' then double click on one of the Order Numbers in range H16:H25 then it will update the table in range P15:U39 Via PivotTable3 located on the Summary Tab

    However, if you select Plant_sys = '0002' it does not work and will not update the table and display the error! I can't figure out why!!
    Last edited by GordonKy42; 06-24-2022 at 11:04 AM.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,245

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

    That zip file is empty...

  8. #8
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    I am not doing very well am i!!! i have tried to upload again but it will not let me even though the Zip is under the 9mb currently 7.5mb

    I have uploaded to the following, hopefully this works

    Removed Link 27/06
    Last edited by GordonKy42; 06-27-2022 at 06:27 AM.

  9. #9
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    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
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    649

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

    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.
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  11. #11
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    Quote Originally Posted by CheeseSandwich View Post
    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.

  12. #12
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    649

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

    Does it work if you do it as below:
    Please Login or Register  to view this content.
    I think excel is having a hard time as the column is mixed with text and numbers - the numbers are also too large to convert to a long. The above will let excel decide what to store the value fVal as depending on what is assigned to it.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    Hey,

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

    Attachment 785299

  14. #14
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    649

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

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

  15. #15
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    Quote Originally Posted by CheeseSandwich View Post
    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

  16. #16
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    649

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

    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.
    Last edited by CheeseSandwich; 06-27-2022 at 09:55 AM.

  17. #17
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    Quote Originally Posted by CheeseSandwich View Post
    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
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    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.

  19. #19
    Valued Forum Contributor
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    2016 & 365 Sub
    Posts
    649

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

    Have a look at the attached.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    08-07-2019
    Location
    Scotland
    MS-Off Ver
    2016
    Posts
    56

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

    Quote Originally Posted by CheeseSandwich View Post
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Error message: Run-Time error '1004 '; application-defined or object-defined error
    By Davasu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2015, 06:52 AM
  2. VBA ERROR: run time error 1004: Application-defined or Object-defined error in excel 2013
    By AnanthKrishna in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-13-2015, 06:16 AM
  3. excel macro run time error '1004'- Application defined or object defined error
    By kmadan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2014, 09:51 AM
  4. Replies: 1
    Last Post: 03-12-2014, 12:42 PM
  5. [SOLVED] Run time error 1004 Application-Defined or Object-Defined Error - Placing data into next e
    By michelle 1 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-27-2014, 12:57 PM
  6. [SOLVED] Error " Run-time error '1004': application defined or object defined error
    By lengwer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2013, 07:26 AM
  7. [SOLVED] run-time error '1004' application-defined or object-defined error - Excel 2007
    By kaurka in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-06-2012, 02:46 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1