+ Reply to Thread
Results 1 to 13 of 13

Save As / Print PDF using sheet name as file name

  1. #1
    Registered User
    Join Date
    12-11-2019
    Location
    Toronto
    MS-Off Ver
    For Mac 6.31
    Posts
    8

    Save As / Print PDF using sheet name as file name

    I have a workbook with many sheets that each must be individually saved/printed as a pdf every month. I would like the name of the pdf file created to be the sheet name from the workbook (not the workbook name). Each month a few new sheets could be added to the workbook. When I save as / print a pdf, it defaults to the workbook name.

    I'm not overly familiar with macros, but I could record one printing each sheet as a pdf, however:
    - Not sure how to automate the naming of the pdf file (want it to be the sheet name)
    - If I record a macro of printing each individual sheet, it will not pick up the new ones (so would need to modify macro each month for new sheets).

    Any help is appreciated!

    Thanks
    Gord

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Save As / Print PDF using sheet name as file name

    Welcome to the Forum wine gums!

    This code will print the active sheet, using the sheet name as the file name. You will need to decide how you want to invoke the code, and include this in a Sub.

    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    12-11-2019
    Location
    Toronto
    MS-Off Ver
    For Mac 6.31
    Posts
    8

    Re: Save As / Print PDF using sheet name as file name

    Thank you this works well as a macro - however when I select multiple sheets, it only creates one pdf with the name being the first tab selected (but the content of all sheets included).
    What I am hoping is to select multiple sheets, run the macro and have a separate pdf for each sheet selected.
    As I said, I'm not familiar with macros/code needed - can the code provided be adjusted for that?
    Thank you!

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Save As / Print PDF using sheet name as file name

    I think this will do it, but haven't had time to test.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-11-2019
    Location
    Toronto
    MS-Off Ver
    For Mac 6.31
    Posts
    8

    Re: Save As / Print PDF using sheet name as file name

    Thank you!
    I tested with a workbook with three worksheets. If I select 1-3 worksheets, it creates the right number of files (1,2 or 3) all named with the names of the worksheets selected.
    However, the pdf created is the same for all of the files produced. The content of all the pdfs is from whatever sheet happens to be 'active' (showing) as opposed different content for each file (from all the sheets selected.
    Example - if I select Sheet1 and Sheet3, and Sheet3 is 'active'(showing) it produces Sheet1.pdf and Sheet3.pdf. The content of both files is from Sheet3. If I select the same two worksheets, but Sheet1 is active, the content of both pdf files is from Sheet1.

    So maybe this is in the line "ActiveSheet.ExportAsFixedFormat _" - I tried to modify, but got errors (complete lack of knowledge of code for macros).

    Let me know if you have a chance to help,
    Thank you!

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Save As / Print PDF using sheet name as file name

    I'm sorry, I wasn't thorough. Your suspicion was correct.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-11-2019
    Location
    Toronto
    MS-Off Ver
    For Mac 6.31
    Posts
    8

    Re: Save As / Print PDF using sheet name as file name

    Absolutely awesome. Thank you! Works perfectly now - the files end up in an unexpected place, but that's ok I'll figure that out.

    You have no idea how much time you have saved my staff every month. I'll make sure you get the credit

    Thanks again.

  8. #8
    Registered User
    Join Date
    12-11-2019
    Location
    Toronto
    MS-Off Ver
    For Mac 6.31
    Posts
    8

    Re: Save As / Print PDF using sheet name as file name

    I spoke too soon - this time I wasn't thorough.
    The pdf files created 'often' contain the content of all the selected sheets, as opposed to just the content of the sheet for which the file is named. It's strange, it doesn't do it all the time, sometimes the content is correct, sometimes some of the pdfs have the content for all sheets and some have the correct content. I tried to figure out a consistent reason why, but couldn't. At one point it seemed just the pdf for the 'active' sheet contained content for all sheets and the pdfs for the other sheets were fine - but that wasn't consistent. When I tried it on our really big spreadsheet with ~100 sheets selected, the results were inconsistent - but all pdfs had too much content.
    Sorry - I thought we had it!

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Save As / Print PDF using sheet name as file name

    The fact this is intermittent is very challenging. I am not sure how to troubleshoot this without your file. Is the data sensitive?

    Also, we can fix the location if you tell me where you want the PDFs to go.

  10. #10
    Registered User
    Join Date
    12-11-2019
    Location
    Toronto
    MS-Off Ver
    For Mac 6.31
    Posts
    8

    Re: Save As / Print PDF using sheet name as file name

    Thanks. I can't send you the real file, but I can make test one - I'll do that tomorrow. Thanks for being willing to help.

    Best case would be to save the PDFs in whatever folder the excel file with the macro is.

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,531

    Re: Save As / Print PDF using sheet name as file name

    The location is fairly easy:

    Please Login or Register  to view this content.
    If you are on a Mac I believe the "\" needs to be ":". To work on both Windows and Mac you can use Application.PathSeparator.

  12. #12
    Registered User
    Join Date
    12-11-2019
    Location
    Toronto
    MS-Off Ver
    For Mac 6.31
    Posts
    8

    Re: Save As / Print PDF using sheet name as file name

    That worked for the location, thank you. I'm on a Mac (Excel v16.33) and the "\" worked fine. (":" didn't).
    For a couple quick tests, it also worked with the correct content in the correct pdfs - so I'll test some more and with bigger sheets to see if I can replicate the problem from yesterday (content from multiple sheets in the same pdf). I'll let you know!
    Thanks again.

  13. #13
    Registered User
    Join Date
    12-11-2019
    Location
    Toronto
    MS-Off Ver
    For Mac 6.31
    Posts
    8

    Re: Save As / Print PDF using sheet name as file name

    Attached is 'Test sheet pdf small v2.xlsm' with the latest macro included.

    I'm having trouble seeing the patter here - only one of the tests worked completely (Test #3, select sheet Test3 first, shift select sheet Test1), and was correct again after running other tests (Test #8). But then in test #9, the only difference is excluding sheet Test1 and that didn't work.

    My testing (the location of the pdf created was correct in every test - same as .xlsm file - so I didn't include that information below):

    Test #1
    Quit Excel and restarted
    Open workbook
    Select sheet 'Test1', hold down shift, select sheet 'Test3' (Test1, Test2, Test3 all selected)
    Ran macro 'print_active_sheets
    Result:
    • three pdf files, named Test1, Test2, Test3
    • Test1 is 3 pages long with content from sheets Test1,Test2,Test3 (incorrect)
    • Test2 is 1 page long, just content from Test2 (correct)
    • Test3 is 1 page long, just content from Test3 (correct)

    Test #2
    Select sheet 'Test3', hold down shift, select sheet 'Test1' (Test3, Test2, Test1 all selected)
    Ran macro 'print_active_sheets
    Result:
    • three pdf files, named Test1, Test2, Test3
    • Test1 is 1 pages long, just content from Test1 (correct)
    • Test2 is 1 page long, just content from Test2 (correct)
    • Test3 is 1 page long, just content from Test3 (correct)
    • Each pdf file overwrites the existing file from previous test (which is good)

    Test #3
    (identical repeat of Test #2)
    Select sheet 'Test3', hold down shift, select sheet 'Test1' (Test3, Test2, Test1 all selected)
    Ran macro 'print_active_sheets
    Result:
    • three pdf files, named Test1, Test2, Test3
    • Test1 is 1 pages long, just content from Test1 (correct)
    • Test2 is 1 page long, just content from Test2 (correct)
    • Test3 is 1 page long, just content from Test3 (correct)
    • Each pdf file overwrites the existing file from previous test (which is good)

    Test #4
    (Repeat of Test #1, except did not restart excel)
    Select sheet 'Test1', hold down shift, select sheet 'Test3' (Test1, Test2, Test3 all selected)
    Ran macro 'print_active_sheets
    Result:
    • three pdf files, named Test1, Test2, Test3
    • Test1 is 3 pages long with content from sheets Test1,Test2,Test3 (incorrect)
    • Test2 is 1 page long, just content from Test2 (correct)
    • Test3 is 1 page long, just content from Test3 (correct)
    • Each pdf file overwrites the existing file from previous test (which is good)

    Test #5
    (same as Test #1, except selected sheets individually with cmd, not shift)
    Select sheet 'Test1', hold down command, select sheet 'Test2', still holding down command, select sheet 'Test3' (Test1, Test2, Test3 all selected)
    Ran macro 'print_active_sheets
    Result:
    • three pdf files, named Test1, Test2, Test3
    • Test1 is 3 pages long with content from sheets Test1,Test2,Test3 (incorrect)
    • Test2 is 1 page long, just content from Test2 (correct)
    • Test3 is 1 page long, just content from Test3 (correct)
    • Each pdf file overwrites the existing file from previous test (which is good)

    Test #6
    (same as Test #1, except only selecting Test1 and Test 2)
    Select sheet 'Test1', hold down shift select sheet 'Test2' (Test1, Test2 selected)
    Ran macro 'print_active_sheets
    Result:
    • two pdf files, named Test1, Test2
    • Test1 is 2 pages long with content from sheets Test1 and Test2 (incorrect)
    • Test2 is 2 pages long with content from sheets Test1 and Test2 (incorrect)
    • Each pdf file overwrites the existing file from previous test (which is good)

    Test #7
    Same as Test#6, except selected Test2 and Test3
    Same incorrect results (2 pdf files 2 pages long with content from Test2 and Test3 - content in same order)

    Test #8
    Identical repeat of Test #2
    Selected Test3, shift, Test1 - all three sheets selected.
    Same result (all pdfs correct)

    Test #9
    Repeat of Test #8, but only selected Test3 and Test2
    Selected Test3, shift, Test2 - two sheets selected.
    Test2.pdf and Test3.pdf identical with content from both sheets (incorrect)
    Test#9
    Selected Test3, command Test1
    Test1.pdf and Test3.pdf identical with content from both sheets (incorrect)

    Test #10
    Selected Test3, command Test2
    Test2.pdf and Test3.pdf identical with content from both sheets (incorrect)
    Attached Files Attached Files

+ 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. Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.
    By N1ka in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-03-2018, 08:07 PM
  2. [SOLVED] Save selected print area as a pdf file using the text from a specific cells as the file na
    By A440 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2015, 04:14 PM
  3. [SOLVED] print/save as pdf with specified file name
    By enemy_zone in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-04-2015, 11:16 AM
  4. Save multiple sheet/print sheet with one command button
    By GJR in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-23-2014, 03:06 PM
  5. Replies: 0
    Last Post: 04-16-2012, 10:57 AM
  6. Copy Save File As then Print macro
    By larzep in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2011, 10:35 PM
  7. How do I print sheet , with actual file save date in footer?
    By irfy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2006, 03:10 PM

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