I’ve used the code from here: https://www.myonlinetraininghub.com/error-creating-relationship-in-vba-userform-code-to-create-pdf-from-excel-worksheet-then-email-it-with-outlook#comment-48666 - and I am getting the following error:
Run-time error ‘1004’:
Document not saved. The document may be open, or an error may have been encountered when saving.
Debug brings me here:
‘Create the PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ :=False, OpenAfterPublish:=OpenPDFAfterCreating
I've attached my workbook.
Additionally - I am hoping to add to this where the user can use one macro to send every unhidden tab to .pdf/email. (Bonus points if we can get a pop-up selector to "check" which tabs they want to send to a single .pdf/email!!!)
Hi,
As I suspected, the file name contains illegal chars, a date was used, with forward slash separator. The date should be reformatted:
& "_" & Format(CurrentMonth, "yyyy-mm-dd") & ".pdf"
To print multiple sheets in the same pdf, you have to select them before running the code to create the pdf.
Use a code like this:
Dim Cell As Range, SheetList() As Variant, Counter As Integer
Counter = 0
For Each Cell In ThisWorkbook.Worksheets("Sheet1").ListObjects(1).ListColumns(1).DataBodyRange
If Len(Cell) = 0 Then Exit For
If ThisWorkbook.Worksheets(Cell.Text).Visible <> xlSheetVisible Then ThisWorkbook.Worksheets(Cell.Text).Visible = xlSheetVisible
If Cell.Offset(0, 1) Like "Yes" Then
ReDim Preserve SheetList(1 To Counter + 1) As Variant
SheetList(Counter + 1) = Cell.Value
Counter = Counter + 1
End If
Next
ThisWorkbook.Worksheets(SheetList).Select
create_and_email_pdf
ThisWorkbook.Worksheets(1).Select
'Hide sheets
For Each Cell In ThisWorkbook.Worksheets("Sheet1").ListObjects(1).ListColumns(1).DataBodyRange
If Len(Cell) = 0 Then Exit For
If Cell.Offset(0, 2) Like "Yes" Then ThisWorkbook.Worksheets(Cell.Text).Visible = xlSheetHidden
Next
End Sub
This code is based on the setup table from Sheet1 of the attached file, where you can set which sheets must be printed, and which should be hidden after printing to pdf.
Thank you Catalin! So helpful!
I am trying to use the 2nd code you sent for printing certain pages, but when i inserted it into my active workbook (of course copying "Sheet1" over, too), it gives me Run-time error '9': Subscript out of range for the following line of code:
If ThisWorkbook.Worksheets(Cell.Text).Visible <> xlSheetVisible Then ThisWorkbook.Worksheets(Cell.Text).Visible = xlSheetVisible
Is there something I need to do since I moved it to a slightly different workbook? The skeleton of the workbook is the same as my previous attachment, but I just have actual data feeding into the "new" one.
Thanks again for your help. I've been playing around with it for a while and just can't seem to figure out why/how to fix.
There is also a defined name used to create the sheet list, make sure you have it in the other book, its name is... SheetList. Normally, if you copied Sheet1, the name should be transferred automatically, but you sill check the formula to see if it looks exactly the same as in the original sample.
That error you had means that there is no sheet in your book with that name from column 1, does the list of sheets from Sheet1 table look ok?
The sheet names in column one look accurate - and I confirmed that the defined name is SheetList in my database too.
I did change the name of the tab to PrintOptions and updated in the code as well, but I had tried it both ways with the same error.
I've attached the exact workbook I am working with. Maybe you can help with that rather than the one missing data. 🙂
Hi,
The code failed at: 8OZ GUSSRP Range Planning Grap sheet, the full name of that sheet was 8OZ GUSSRP Range Planning Graph
The source of the eror is the formula I set for PrintOptions table, I knew that the limit for sheet names is 30 chars, your sheet has 31 chars, so the formula did not return the complete sheet name in this case:
=IFERROR(MID(INDEX(SheetList,ROW(A11)),FIND("]",INDEX(SheetList,ROW(A11)))+1,30),"")
Change the formula to:
=IFERROR(MID(INDEX(SheetList,ROW(A11)),FIND("]",INDEX(SheetList,ROW(A11)))+1,50),"")
This did it - THANK YOU!!!! 😀
You're welcome, glad to hear that it works as you waned.
