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.
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.
Experience trumps academics every day of the week and twice on Sunday.
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 star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
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.
Thank you. I tried running this code with all relevant sheets visible but it returned Run-time error '9'. Subscript out of range.Originally Posted by sintek
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.
Yes that would happen as you don't have any sheets named Sheet1,2 4 etcbut it returned Run-time error '9'. Subscript out of range.
I suggest uploading a sample file...see top yellow banner re: instructions...and reference this to explain your requirement...
Try so.
The information for the file path in A1, A2 and A3 does not tell us anything. Need a proper explanation about each cell's contentsPlease Login or Register to view this content.
Last edited by jolivanes; 01-24-2024 at 01:07 AM.
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.
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.
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.
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.
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...
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.
Thanks.
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.
Last edited by tlc53; 02-13-2024 at 04:58 PM.
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.
You don't have a "Dairy Report" Sheet in your WorkbookPlease Login or Register to view this content.
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!).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.
You don't have a "Dairy Report" Sheet in your WorkbookPlease Login or Register to view this content.
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.
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.
Thanks for the update and good luck.
You know where to go to if help is needed.
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.
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.
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.
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
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.
Last edited by sintek; 02-14-2024 at 04:09 AM.
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!!
..........................
THANKS.gif
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks