+ Reply to Thread
Results 1 to 6 of 6

Looping through Slicer and Saving to PDF - Error

  1. #1
    Registered User
    Join Date
    03-26-2021
    Location
    Austin, TX
    MS-Off Ver
    365 Pro Plus
    Posts
    3

    Looping through Slicer and Saving to PDF - Error

    Hi There!

    I am having an error trying to save to PDF as the code cycles through the Slicer. I borrowed this code from a 4 year old thread on the site: 1184223-macro-for-looping-through-slicer-and-saving-as-pdf-writing-over-same-pdf.html - sorry I can't post a link yet!

    But it continues to break at the PDF section. When I remove it, I can see that it is cycling through the slicer. I am not super well versed in VBA's, but have successfully used VBA's that cycle through dropdown menus and save to PDF. I am getting run-time error code 5 - invalid procedure call or argument

    Any help would be appreciated! Thank you in advance!

    Also I am referencing cell D1 - as a cell that is referencing the Pivot Table's cell from another sheet - please let me know if that is wrong


    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by kmunoz; 03-26-2021 at 04:58 PM.

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,901

    Re: Looping through Slicer and Saving to PDF - Error

    Possibly...
    Please Login or Register  to view this content.
    ____________________________________________________________
    If I've been helpful, let me know. If I haven't, let me know that too.

  3. #3
    Registered User
    Join Date
    03-26-2021
    Location
    Austin, TX
    MS-Off Ver
    365 Pro Plus
    Posts
    3

    Re: Looping through Slicer and Saving to PDF - Error

    Awesome!! Thank you SO much this worked!! I did get an error when I initially copied it in with the sPath, so I removed it from the code. It did start saving the PDFs into a folder I had open. What can I change so that it requires the user to choose the folder?

    I truly appreciate your help!

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,901

    Re: Looping through Slicer and Saving to PDF - Error

    Try replacing this:
    Please Login or Register  to view this content.
    with this:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-26-2021
    Location
    Austin, TX
    MS-Off Ver
    365 Pro Plus
    Posts
    3

    Re: Looping through Slicer and Saving to PDF - Error

    Thank you SO much! That fixed it. I did figure out that I did have to change the filename at the bottom to include the "\" as well - but it worked great! I really appreciate your assistance.

    I did have one more question - I am so sorry!
    If I want to change the Sheet name from "Sheet2" --> "Teacher" I am finding that the macro breaks - what might I be doing wrong?
    I also am wondering if the part that says "Sheet4" down towards the bottom (Sheet4.ExportAsFixedFormat - is that something that can't be changed or needs to also update to something different if I change the sheet name

    Please Login or Register  to view this content.

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,901

    Re: Looping through Slicer and Saving to PDF - Error

    The way the code is written, Sheet2 is a code name for a worksheet in the workbook containing the code. If Teacher isn't a code name of a worksheet in the workbook holding the code, it will fail.

    Try replacing Sheet2 with ActiveWorkbook.Worksheets("Teacher").

    Use a similar procedure for the code name Sheet4.


    If you still have problems, follow the directions in the yellow banner at the top of the thread to post your workbook.

+ 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. Loop through slicer, while also selecting single slicer value on separate slicer
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2018, 01:41 PM
  2. Replies: 1
    Last Post: 04-23-2018, 09:43 AM
  3. [SOLVED] Macro for Looping Through Slicer and Saving as PDF writing over same PDF
    By Kelso9696 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-05-2017, 05:05 PM
  4. looping through folders - saving xls files as PDFs
    By Hugh Jass in forum Excel General
    Replies: 1
    Last Post: 05-11-2016, 06:06 PM
  5. looping through folders - saving xls files as PDFs
    By Hugh Jass in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2016, 06:06 PM
  6. Importing, looping and saving (not really solved)
    By stevencohen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2011, 02:10 AM

Tags for this Thread

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