+ Reply to Thread
Results 1 to 10 of 10

Excel VBA to save worksheet to PDF

  1. #1
    Registered User
    Join Date
    06-11-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Excel VBA to save worksheet to PDF

    I have a macro which when run, will go through a dropdown list and print the spreadsheet for each one. However I would like to print a separate sheet to pdf

    So I have a couple of questions, they are relatively simple I think.
    1) I want to save each page of the list as a pdf, can I reference a cell as a file name? I think I put in a line of code which says ' Set Filename="H1", but then I am not sure how to reference this later on.
    2) how do I reference a different 'ActiveSheet', i.e. the page to printout is not the one with the list on.


    Please Login or Register  to view this content.
    Last edited by LMoir; 03-11-2014 at 12:35 PM.

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Excel VBA to save worksheet to PDF

    Dim Filename as String and do not use Set since it is not an object like a Range is.

    This may be of some use.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-11-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Excel VBA to save worksheet to PDF

    Hi

    thanks for this. Is this 2 ways of PDFing ie saving and print?

    How do I specify which is the sheet to print out?

    sorry not very clued up when it comes to coding

  4. #4
    Registered User
    Join Date
    06-11-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Excel VBA to save worksheet to PDF

    actually worked out how to do it.

    I've just used the print to pdf currently, however it keeps prompting where to save for each print. Is this only because I am printing rather than saving to pdf? Can I get around this?

    thanks
    Lizzy

  5. #5
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Excel VBA to save worksheet to PDF

    When you use the method to print to a PDF printer, yes, it will prompt for the filename. This is one reason why the method that I posted is handy. The first input parameter lets you set the filename.
    Just replace or comment out this line to stop the prompt for a filename in my Sub. Or, just use parts of the Sub that I posted. I guess that I could have made prompting for a filename as an Optional input parameter.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-11-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Excel VBA to save worksheet to PDF

    sorry I meant to top one.
    Below is what I have in the code, can you suggest what to take out/put in to stop a prompt

    Please Login or Register  to view this content.
    Thanks

  7. #7
    Registered User
    Join Date
    06-11-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Excel VBA to save worksheet to PDF

    ah I've just realised it doesn't save into the folder at all, I just happened to have been in the correct position.
    Sorry I know I'm being annoying.

  8. #8
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Excel VBA to save worksheet to PDF

    Please Login or Register  to view this content.
    Last edited by Kenneth Hobson; 03-14-2014 at 09:21 AM.

  9. #9
    Registered User
    Join Date
    06-11-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Excel VBA to save worksheet to PDF

    when I remove the rc=application........ it then doesn't save it at all

  10. #10
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Excel VBA to save worksheet to PDF

    Of course it is just as easy to just use the code in the Sub directly no more complicated than it is.
    Please Login or Register  to view this content.

+ 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] Excel warning when save worksheet.
    By Jack40 in forum Excel General
    Replies: 3
    Last Post: 08-06-2012, 05:31 AM
  2. Excel 2007 : Excel refuses to save my worksheet
    By cioangel in forum Excel General
    Replies: 0
    Last Post: 07-14-2010, 03:26 PM
  3. Save Worksheet to New Excel File
    By BrownTeddyBear in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-26-2009, 01:09 AM
  4. Save excel worksheet as PDF
    By Mark Brown in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-03-2009, 03:36 PM
  5. [SOLVED] when I save a worksheet made in excel 2003 to excel 97,
    By apan2so in forum Excel General
    Replies: 0
    Last Post: 03-12-2006, 10:15 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1