VBA Macro to Save each Excel sheet in a workbook as its own separate PDF based on the Value in a Cell D9 on each sheet

  • Hello all,


    I am wondering if anyone could please kindly help me


    I need a VBA Macro to save each Excel sheet in a workbook as its own separate PDF based on the Value in a Cell (D9) on each sheet. I have the code to name by tab name but I can not work out how to save by cell reference.


    The code I have to save by tab ref is as follows, I just need to alter it to used cell D9 as the saved Pdf file name




    If anyone knows how to do this I would be so happy as its driving me nuts


    Many Thanks


    Debs

  • Thank you kindly to royUK for you fast reply and help with using this forum, this is my first time using anything like this.


    Apologies I wasn't clear with my request as I need a VBA to save just as the information contained in cell D9 as the PDF file name and not the worksheet name as well.


    When I remove the piece of script pointing to the worksheets & ws.Name & "_" I get an error message that states Time-run error -'214024773 (8007007b)' document not saved.


    Also I believe I have an issue with the end of my script as it will run and save all 40 sheets in the workbook but then I get a different Time-Run error '1004' Document not saved. The document my be open or an error may have been encountered when saving. The workbook than just hangs for a few minutes.


    I know you have already helped me but if you know how to resolve these issues that would be fantastic


    Many thanks

  • Hello,


    Could you prepare a sample file with say 3 to 4 sheets ... and no confidential data ...


    Attaching such a file would solve your question in a matter of minutes ...;)


    Meanwhile you could test following


    Code
    1. Sub SaveWorksheetAsPDF()
    2. Dim ws As Worksheet
    3. On Error Resume Next
    4. For Each ws In Worksheets
    5. ws.ExportAsFixedFormat xlTypePDF, "C:\Users\lucy\Dropbox\certs\" & ws.Range("D9") & ".PDF"
    6. Next ws
    7. On Error GoTo 0
    8. End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().

  • Does this do what you want


  • Actually this might be better




  • Post by royUK ().

    This post was deleted by the author themselves ().
  • Dear Carim,


    thank you kindly for your e-mail.


    I have created a blank version of my workbook and attached it as per you suggestion.


    If possible I would like a code to save the tabs as per the info in D9

    Plot 1

    Plot 2

    Plot 3

    Plot 4

    Plot 5

    ect as per the information contained in cell D9 of each sheet so the final result is the workbook remains looking and functioning as it is but I have deparate PDF's of each sheet that if the code works will come out as 20 Debbies place,

  • Ops returned my post too soon there


    Plot 1 This would return a saved PDF called 20 Debbies place

    Plot 2 This would return a saved PDF called 21 Debbies place

    Plot 3 This would return a saved PDF called 22 Debbies place

    Plot 4 This would return a saved PDF called 23 Debbies place

    Plot 5 This would return a saved PDF called 24 Debbies place and so on


    I have 40 plot per work sheets and ideally I would like the PDF's to start saving at the first plt tab and end on the 40th but if this is not achievably then I am happy to have all sheets saved as per cell D9 and delete those which are not needed.


    Any help you can give me would be so appreciated


    Kind regards


    Debbie

  • Re,


    If my understanding is correct ... the only thing you are missing is the restriction to the ' Plot ' sheets ...


    Code
    1. Sub SaveWorksheetAsPDF()
    2. Dim ws As Worksheet
    3. For Each ws In Worksheets
    4. If Left(ws.Name, 4) = "Plot" Then
    5. ws.ExportAsFixedFormat xlTypePDF, "C:\Users\lucy\Dropbox\" & ws.Range("D9").Value
    6. End If
    7. Next ws
    8. End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Apologises I didn't see all these messages until a short while ago I was looking to my e-mail for answers


    royUK


    With your first script nothing appeared in dropbox


    Second script gave an error message as follows

    Compile error:

    Method or data member not found and it highlighted the piece of the script that said .TempSht.copy


    Third script gave an error message as follows

    Compile error:

    Ambiguous name detected: SaveWorksheetAsPDF


    Carim


    I am unsure where to insert the piece of the script that you have sent me (Code, 8 lines)

    I have tried it in a few place but cant get it to work


    Thank you both for your help though, it most kind of both of you

  • Hello,


    Attached is your test file


    You can click on the Buttton : Save As PDF

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Sorry, trying to do too many things at once as I am on my own in the office. It was this script from Carim which ran but nothing appeared in the Dropbox


    1. Sub SaveWorksheetAsPDF()
    2. Dim ws As Worksheet
    3. On Error Resume Next
    4. For Each ws In Worksheets
    5. ws.ExportAsFixedFormat xlTypePDF, "C:\Users\lucy\Dropbox\certs\" & ws.Range("D9") & ".PDF"
    6. Next ws
    7. On Error GoTo 0
    8. End Sub
  • Hello again,


    You should be testing the very last version in message # 15 ...


    But there is something quite certain ... you must be 100 % certain about your destination directory :


    C:\Users\lucy\Dropbox\certs\


    or


    C:\Users\lucy\Dropbox\


    otherwise you will always keep on getting errors ...!!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Dear Carim,


    You genius. I am working off both a Mac for Emails and using a PC to building this program and to talk on this site. The email on the mac just said (code, 8 lines) which confused me but I have found your code and just a moment ago run the test sheet you kindly altered and that worked.


    I am just opening the actual version to add this script and try running it to make sure it works on the live version but so far so good:):thumbup:

  • Dear Carim,


    Its not working on my master but it maybe because I have parts of the sheets locked and protected.


    I will keep trying as I know it works on the test workbook you sent


    Thanks a million for your patients and help, it is greatly appreciated


    Kindest regards


    Debbie