+ Reply to Thread
Results 1 to 27 of 27

VBA Code to Save Multiple Sheets to PDF

  1. #1
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    VBA Code to Save Multiple Sheets to PDF

    Hi there,

    I have this VBA code which works perfectly for when I want to save one sheet to pdf. However, I would like to select multiple sheets to save as one pdf (Sheet 1, 2 & 3 for example).

    Can someone please tell me how I can alter my code to do this?

    Thanks so much!

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: VBA Code to Save Multiple Sheets to PDF

    How are you deciding which sheets?
    I just saw that you also have hidden sheets in the workbook so you can't use selecting the sheets which would be the easiest.
    Last edited by jolivanes; 01-22-2024 at 11:51 PM.
    The inherent weakness of the liberal society: a too rosy view of humanity.

  3. #3
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,297

    Re: VBA Code to Save Multiple Sheets to PDF

    Could be as simple as...but as joivanes says...depends on your actual setup and structure taking into consideration hidden sheets
    Please Login or Register  to view this content.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  4. #4
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    My spreadsheet contains Data Entry sheets and Report sheets. The sheets hide/unhide depending on the options selected on the General Setup page (Sheet2).

    The individual PDF reports (as per my code example) are run from a drop-down menu (cell K5), which is created by referring to a changing VLOOKUP formula list (U1:U10).

    If all options were selected, the list would look as follows;

    U1 Please Select
    U2 All Reports
    U3 Header Report (Sheet1)
    U4 Cashflow Report (Sheet12)
    U5 Milksolids Report (Sheet3)
    U6 Dairy Report (Sheet21)
    U7 Beef Report (Sheet10)
    U8 Sheep Report (Sheet14)
    U9 Annual Report (Sheet8)
    U10 Analysis Report (Sheet16)

    I was hoping that if I had a VBA code that could just refer to all the Report sheets, it would still only select/pdf visible sheets, which is what I would want. However, I'm not sure if it would operate like this.

    The General Setup page (Sheet2) which the code runs from, will also contain information for the file path (A1, A2, A3).

    I have tried altering my current code to adapt for this, but my coding knowledge isn't good enough and it's returned nothing but errors. I really appreciate looking at this!

    Let me know if you need any other information. I'm new to this site but I've figured out where the notifications go, so I'll be much quicker replying next time

    Thanks.

  5. #5
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    Quote Originally Posted by sintek View Post
    Could be as simple as...but as joivanes says...depends on your actual setup and structure taking into consideration hidden sheets
    Please Login or Register  to view this content.
    Thank you. I tried running this code with all relevant sheets visible but it returned Run-time error '9'. Subscript out of range.

  6. #6
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,297

    Re: VBA Code to Save Multiple Sheets to PDF

    but it returned Run-time error '9'. Subscript out of range.
    Yes that would happen as you don't have any sheets named Sheet1,2 4 etc
    I suggest uploading a sample file...see top yellow banner re: instructions...and reference this to explain your requirement...

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: VBA Code to Save Multiple Sheets to PDF

    Try so.
    Please Login or Register  to view this content.
    The information for the file path in A1, A2 and A3 does not tell us anything. Need a proper explanation about each cell's contents
    Last edited by jolivanes; 01-24-2024 at 01:07 AM.

  8. #8
    Registered User
    Join Date
    10-01-2014
    Location
    Romania
    MS-Off Ver
    MS Office 2010 Pro Plus
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    This is what I made for 5 Sheets,
    all 5 sheets are xlVeryHidden, the code unhides them, then saves them as PDF, and hides them back, feel free to edit as you wish.
    In the sheet "data", G2 is actual today's date, so it includes that in the name of the PDF.
    Also it checks if any of the 5 sheets are empty, it will exclude it from selection.

    Private Sub SAVE_PDF_Click()
    Dim strPath As String
    Dim myFile As Variant
    Dim strFile As String
    Dim Wsname As Variant
    Dim ws As Worksheet
    Dim includeSheet As Boolean
    Dim exportSheets() As String
    Dim exportCount As Integer
    On Error GoTo errHandler
    Application.ScreenUpdating = False
    Call FilterBeforePrint
    ReDim exportSheets(1 To 5)
    exportCount = 0
    For Each Wsname In Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")
    includeSheet = CheckSheetContent(ThisWorkbook.Sheets(Wsname))
    If includeSheet Then
    exportCount = exportCount + 1
    exportSheets(exportCount) = Wsname
    Worksheets(Wsname).Visible = True
    Else
    End If
    Next
    strFile = Replace(Replace("Sheet1", " ", ""), ".", "_") _
    & "_" _
    & "Sheetname_PDF" & Format(ThisWorkbook.Worksheets("data").Range("G2").value, "dd-mmm-yyyy") & ".pdf" 'creates name of pdf file
    strFile = ThisWorkbook.Path & "" & strFile
    myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strFile, _
    FileFilter:="PDF Files (*.pdf), *.pdf", _
    Title:="Select Folder and FileName to save")
    If myFile <> "False" Then
    ReDim Preserve exportSheets(1 To exportCount)
    ThisWorkbook.Sheets(exportSheets).Select
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=myFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False 'exports array as PDF
    For Each Wsname In Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")
    If IsInArray(Wsname, exportSheets) Then
    Worksheets(Wsname).Visible = xlVeryHidden
    End If
    Next
    MsgBox "Your PDF has been created."
    ThisWorkbook.Save
    End If
    Exithandler:
    Application.ScreenUpdating = True
    Exit Sub
    errHandler:
    MsgBox "Could not create your PDF!"
    Resume Exithandler
    End Sub
    Last edited by makeeuropeanu; 01-25-2024 at 10:38 AM.

  9. #9
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    Thank you for all the suggestions! Looking at these I was able to compile the below code, which is the closest I have come to it working.

    If all relevant sheets are unhidden, then this code successfully saves a pdf. However, the sheets are not in the order, as laid out in the code but rather, the actual position the sheets are in on the spreadsheet (eg. Header Report is the last sheet in the spreadsheet, but I would want it to be the first in the pdf). If I have to physically re-position the sheets to correct this, then I will do so.

    With this code, I was hoping when it selected the sheets and it found that one was not visible, it would just ignore it, and just select the sheets in the list that are visible. Unfortunately, this is not the case and it returns a: Run-time error 1004 - Select method of Sheets class failed.

    So I guess I either need to work out a way around this error, so it ignores hidden sheets in the select list, or somehow select my sheets from my VLOOKUP list located in U1:U10 (ignoring blanks, which will be hidden sheets).

    I should also mention, when a sheet is hidden/not required, it does not mean it is blank. It has a header, logo, title etc.

    If anyone has any suggestions, they would be greatly received.

    Thanks!

    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: VBA Code to Save Multiple Sheets to PDF

    From Post #4: "I was hoping that if I had a VBA code that could just refer to all the Report sheets"

    In Post #9 in the Array of Sheet Names, there are 4 Sheets Where "Report" is part of the Name out of 10 Sheet Names.

    If you don't give people the proper data to work with, you'll be here for a week of Sundays.

  11. #11
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: VBA Code to Save Multiple Sheets to PDF

    You can try this.
    In prshtArr, enter the Sheet Names in the order you want them to appear in the PDF file. Does not matter if they're hidden or not.
    Change the Folder and File Name in the PDF line as required.
    The k and kk snippets can be changed if you want to closing the workbook without saving and opening again if needed.

    Please Login or Register  to view this content.
    Last edited by jolivanes; 01-29-2024 at 12:10 AM.

  12. #12
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,297

    Re: VBA Code to Save Multiple Sheets to PDF

    I can see this going round and round in circles...
    @ tlc53...I suggest uploading a sample file depicting your actual file setup and explain in detail step by step what it is you are trying to achieve...
    See yellow banner above...

  13. #13
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    Thank you. However, I couldn't get this code to work. It returned Run-time Error '9' - Subscript out of range.

    It highlighted the second line of code as shown in the extract below.

    I'm going to try and upload a copy of the spreadsheet (I've not done this before). I have saved your code under Module10.


    Quote Originally Posted by jolivanes View Post
    You can try this.

    For j = LBound(prshtArr) To UBound(prshtArr)
    Sheets(prshtArr(j)).Move After:=Sheets(Sheets.Count - l)
    Thanks.

  14. #14
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    I'm hoping this will attach the spreadsheet.
    The "General Setup" sheet is where I run the PDF codes from (K5).

    Module 9 is the closest I have got to getting this to work (run manually) but it only works if sheets are unhidden and it doesn't save in the listed order.

    Module 10 has jolivanes code, which came back with a run-time error.
    Attached Files Attached Files
    Last edited by tlc53; 02-13-2024 at 04:58 PM.

  15. #15
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: VBA Code to Save Multiple Sheets to PDF

    Add an s in this line "thiSht = ActiveSheet.Name" to make it "thisSht = ActiveSheet.Name"

    However, that is not why it did not work. It works if you check your sheet names.
    Please Login or Register  to view this content.
    You don't have a "Dairy Report" Sheet in your Workbook

  16. #16
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    Quote Originally Posted by jolivanes View Post
    Add an s in this line "thiSht = ActiveSheet.Name" to make it "thisSht = ActiveSheet.Name"

    However, that is not why it did not work. It works if you check your sheet names.
    Please Login or Register  to view this content.
    You don't have a "Dairy Report" Sheet in your Workbook
    It's an older version of my spreadsheet, so it was incorrect to have "Dairy Report". I've taken that out of the code now (sorry!).

    I updated the code with the extra "S" and it now returns "Run-time error '1004' - Document not saved. The document may be open, or an error may have been encountered when saving". When I debug, it highlights this line of code..

    Please Login or Register  to view this content.

  17. #17
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: VBA Code to Save Multiple Sheets to PDF

    Check your 7th line where PDF is declared that everything is right.
    Excel, and all other programs rely on proper spelling and no differences in values and/or text in cases like this.
    That extra "s" has nothing to do with the working of the code. (BTW, you showed a capital S in Post #16. The code should make that a smaller s automatically I think)

  18. #18
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    Quote Originally Posted by jolivanes View Post
    Check your 7th line where PDF is declared that everything is right.
    Excel, and all other programs rely on proper spelling and no differences in values and/or text in cases like this.
    That extra "s" has nothing to do with the working of the code. (BTW, you showed a capital S in Post #16. The code should make that a smaller s automatically I think)
    Thank you for your patience
    The code is running now! I'm going to see if I can now adapt it, so the saving process is automated (rather than hard written into the code). Looking very promising though. Thank you!
    I'll let you know how I go.

  19. #19
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: VBA Code to Save Multiple Sheets to PDF

    Thanks for the update and good luck.
    You know where to go to if help is needed.

  20. #20
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    I have used the below code and included all the report sheet names it would need to pdf (if all options/sheets were visible.

    Things it is doing correctly;
    It saves the reports in the correct order, in the pdf.
    Once the code has finished running, it correctly re-hides any Report sheets that were originally hidden.

    Things it is not doing correctly;
    It saves all Report sheets to PDF, not just the ones that are visible/not hidden.
    It takes 1 minute 40 seconds for this code to run.

    Goodness me this isn't easy. Any ideas on amendments I could try? Thank you.
     
    Please Login or Register  to view this content.

  21. #21
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

    Re: VBA Code to Save Multiple Sheets to PDF

    It should only print the sheets that are in prshtArr. That's why you put them in that array. If you don't want them printed, don't put them in.

  22. #22
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,297

    Re: VBA Code to Save Multiple Sheets to PDF

    I personally think, you need to start over...Your sample file does not at all represent your explanation in Post 1...

    So, making use of your sample file, what is it you actually want to achieve when making a selection in K5...
    Please don't reference any of the above code...Just explian in step by step detail what and why...
    This is what K5 has as selections?

    Untitled.png


    As a side note:...SO SO SAD, that you make use of merged cells in your entire file...why I have no idea!
    Last edited by Sintek; 02-12-2024 at 02:24 AM.

  23. #23
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    Quote Originally Posted by sintek View Post
    I personally think, you need to start over...Your sample file does not at all represent your explanation in Post 1...

    So, making use of your sample file, what is it you actually want to achieve when making a selection in K5...
    Please don't reference any of the above code...Just explian in step by step detail what and why...
    This is what K5 has as selections?

    As a side note:...SO SO SAD, that you make use of merged cells in your entire file...why I have no idea!
    Thank you sintek. I will start-over and attach a new sample file.

    My spreadsheet has a 'Save-Reports Menu' on the 'General Setup' page. From this drop-down menu, you can select whichever report you want to save to PDF.
    The 'Save-Reports Menu' options change, depending on the options selected on the 'General Setup' page. For example, if Sheep Stock option in I5 was set to 'NO', the 'Sheep Report' will be removed from the 'Save-Reports Menu" and the Sheep Stock sheets would remain hidden. Based on the options selected, the available reports will change accordingly, and the sheets/reports not required, will stay hidden.

    All the single saving of reports to PDF work fine. However, now I would like an option that saves all the available/selected reports to one PDF, so that it forms a complete report. The complete report needs to save, in the same order that they are listed in, in the 'Save-Reports Menu" (PDF_Menu). So, starting with the 'Header Report', then 'Cashflow Report', 'Milksolids Report', etc. So it's not necessarily all the reports that need to be included, only those that the options have been turned on for and are therefore listed in the 'Save-Reports Menu' - (gosh, I hope that makes sense).

    In regards to the merged cells, I'm a little nervous to ask why they're so bad, but I'm happy to remove most of them if it helps

    Really appreciate you looking at this. Thanks!

    Let me know if I need to explain anything further.

    Farmers Cashflow 2023 - TEST 140224.xlsm

  24. #24
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,660

  25. #25
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,297

    Re: VBA Code to Save Multiple Sheets to PDF

    Lets focus on basics first...
    If a specific sheet is selected then only that sheet is to be exported...

    If all reports are selected then only those listed based on "values from formulas" in Col U are to be exported in the order shown in this list...
    For this the sheets will have to be sorted in order of the list for the pdf export order...

    Does the order of your sheets matter in the file...With so many, I doubt it makes a difference...
    Making use of quick navigation should help...

    Untitled.png

    For testing I've just used workbook path and sheet name...
    Let's run from a button first and can later amend for Worksheet_Change Event...
    Please Login or Register  to view this content.

    PS...I have removed your worksheet activate code for testing...don't know why it is there anyway...

    Some advice for your next build...When you find the need to have to incorporate sheet protection, making some visible and others not...having some rows visible and others not etc etc etc ... I suggest building a Userform interface with application not visible...also, set up your sheets so you don't need to hide rows ets etc...

    This here below...Is a nightmare...All that in a Worksheet_Change Event...I had to remove 9000+ characters just to display the code here...

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Sintek; 02-14-2024 at 04:09 AM.

  26. #26
    Registered User
    Join Date
    01-22-2024
    Location
    Thames, New Zealand
    MS-Off Ver
    Microsoft 365
    Posts
    11

    Re: VBA Code to Save Multiple Sheets to PDF

    Quote Originally Posted by sintek View Post
    Lets focus on basics first...
    If a specific sheet is selected then only that sheet is to be exported...
    Whoop whoop!! Thank you so much Sintek!!
    I amended your code slightly, so that it saves like my other codes, and it works great. I do want the sheet order to remain the same, but I'll just run another code to re-order them. So that's no problem.

    In future I'll make sure I pose my question clearly and have all the relevant information included in my attachment.

    As you can probably tell, I have no formal training. I appreciate your help so much and I'm so happy this issue is now resolved.

    I'll have a look at the Userform Interface you mention too.

    Thanks again!!

  27. #27
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,297

    Re: VBA Code to Save Multiple Sheets to PDF

    ..........................
    THANKS.gif

+ 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] Multiple sheets select based on values in sheets, save to pdf, code update
    By vendam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2020, 11:37 AM
  2. Change Code to save in selected sheets with inputboks
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-22-2020, 12:51 PM
  3. Macro to save multiple sheets to multiple PDF with cell value as file name
    By thekatsmiaow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2014, 05:17 AM
  4. Macro To save multiple Excel sheets to multiple (individual) PDF Files
    By Keith Edgar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-21-2014, 12:51 PM
  5. Save Multiple Excel Sheets from a Workbook into Multiple Text Macintosh Format VBA
    By ashokpatidar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-05-2013, 10:04 PM
  6. Replies: 2
    Last Post: 09-19-2013, 04:22 AM
  7. VBA Code that creates new workbook that has 2 sheets and auto save
    By jasmin1279 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-21-2011, 10:38 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