+ Reply to Thread
Results 1 to 6 of 6

Slicer cache causing issues in VBA

  1. 06-30-2015, 02:06 AM #1
    Tip1985 is offline
    Registered User
    Join Date
    06-24-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    8

    Slicer cache causing issues in VBA

    Hi,

    I am trying to get code to create a pivot table and slicer out of the data on each sheet.

    I started by recording a macro then copying it into VBA and copying the code across each sheet for every month changing the names of the month.

    The Jul and Aug sheets seem to work when I run the Step 4 - Apply Pivot Table command but when I run the Sept sheet with the same data I get

    Run Time error '1004'
    The item with the specified name wasn't found.

    Please Login or Register  to view this content.
    It is a work in progress and I'm not sure I'm going about it the right way so any recommendations would be appreciated.

    Thanks
    Tom
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-30-2015, 02:49 AM #2
    mangesh.mehendale is offline
    Valued Forum Contributor mangesh.mehendale's Avatar
    Join Date
    06-04-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    510

    Re: Slicer cache causing issues in VBA

    Hiii,
    Please go through following link
    https://support.microsoft.com/en-us/kb/940166
    Reply With QuoteRegister To Reply
  3. 06-30-2015, 03:03 AM #3
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Slicer cache causing issues in VBA

    you trying to make a slicer that probably already exist
    try this for code
    i took out pivot table version as i don't have xl2013

    Please Login or Register  to view this content.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 06-30-2015, 03:58 AM #4
    Tip1985 is offline
    Registered User
    Join Date
    06-24-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    8

    Re: Slicer cache causing issues in VBA

    Hi Mangesh.mehendale,

    I followed the instructions on the link you provided and it puts the pivot table on another sheet. Also, it did not rectify the initial problem.

    Humdingaling,

    Thanks so much. Your code works like a gem. I'll go back and try to work out what you did. I'm new to this game as you can probably tell.

    Cheers
    Tom
    Reply With QuoteRegister To Reply
  5. 06-30-2015, 09:17 PM #5
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Slicer cache causing issues in VBA

    not a problem
    it was actually your last couple of lines causing the issue
    Please Login or Register  to view this content.
    array(category 2) is a defined name for an object so you cant use the same one more than once in a workbook
    the last 3 lines is to select object and move it

    to circumvent needing this and thereby avoiding the issue, i added it into the slicer insertion code itself

    Please Login or Register  to view this content.
    the last 4 numbers are left, top, width,height
    change them if you want to move the slicer to a
    different position...using left and top
    different size....using width and height

    ps tip for your macro recording then fixing in the future (i started this myself)
    to help you remove some clutter

    code like 
    Please Login or Register  to view this content.
    aren't necessarily needed most of the time...all it does is move the window...so unless you want to move the window you can delete it
    Reply With QuoteRegister To Reply
  6. 07-01-2015, 03:53 AM #6
    Tip1985 is offline
    Registered User
    Join Date
    06-24-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    8

    Re: Slicer cache causing issues in VBA

    Hi Humdigaling,

    Thanks very much for your feedback. Much appreciated.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Volatile Functions causing issues
    By penfold1992 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-21-2014, 10:40 AM
  2. [SOLVED] Complex combining of formulas causing me issues
    By phpolicylady in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-10-2014, 10:14 AM
  3. External Links Causing Issues
    By kushalb in forum Excel General
    Replies: 4
    Last Post: 05-23-2011, 06:27 AM
  4. Negative value causing issues
    By Speedbird1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-01-2008, 10:24 AM
  5. [SOLVED] If then statement causing value issues in Macros
    By ssciarrino in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2005, 02:10 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1