Export Multiple Sheets to a Single PDF - Excel VBA
ฝัง
- เผยแพร่เมื่อ 11 ก.ย. 2024
- Export multiple sheets to a single PDF using Excel VBA. This video tutorial explains each element of the code, which can be found here - goo.gl/xaKjvr
During the video, you can see how to export specific sheets to a single PDF. You can reference these sheets by their name or index number.
The video also explains how to adapt the code to export the currently selected sheets.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
![[FULL EP.73] เปิดบ้าน 70 ล้าน "แป้ง Zbing" ยกมาทั้งญี่ปุ่น l เคหสถานบานปลาย l One Playground](http://i.ytimg.com/vi/YBnJhTt07Pg/mqdefault.jpg)
Mate, ur great! Thank you!
Thank you.
Hi There, Great tutorial thank you. I've successfully got the VBA to work but no matter what order I put the sheets in, I still get the same sheet at the front. Is there way to determine the order in which the sheets export to PDF? Also, each folder can only be made once - then I get a Path/File access error because the VBA is attempted to create a folder that already exists by the same name..is there any solution to this? Thanks so much
Why im error MKDir
I try to use button click
Great Tutorial !!!!
This was exactly what I needed. Thank you so much! I do have a question.... When I manually export either of the sheets individually as a PDF just using the Export option, it looks fine. When I run the macro it does exactly what I want and perfectly concatenates the sheets together into one pdf, BUT I have random lines of text that are bold. None of the text on either of the two sheets is set to bold and this is in the middle of long strings of text randomly... any idea why it does that? It was when I emailed it to someone and then I opened it from that email to double check it... some random lines are bold. If I open it in Acrobat on my desktop, it is fine. This is for catering contracts for our restaurants so I want it to look professional and cant figure out what is causing this.
CODE line for name PDF based on cell value from chosen shet, i have teamplate sheet to make others
Sir how to auto save data save in PDF if data lond aur shot rows and column in excel vba
Hi Alan, I'm a novice and have spent hours trying to figure this one out with no luck... hoping you might have a tutorial for this?
I want to click on a command button that will save a spreadsheet to a folder with a file name that is in a cell, then export sheets 6 and 7 to a PDF on desktop with the same file name, and finally email to a supplier.
I have written the same type of code where one sheet is exported as pdf. It works on my computer without any problem but it fails on another computer. My excel version is Office 2016 home edition and where it failed it has a version Office 2016 pro. It frustrates me the most. What could possibly be the reason, I could not understand.
great tutorial, very helpful. thank you very much. I wanted to ask a question about the ability to just have a link in that same excel sheet where you can just click and those PDFs will open in a new window. instead of saving them in a folder.
Thank you Frederick. No reason why this can't be done. The PDFs would need to be saved before a link can open them in that format though.
when i am exporting the sheets it is exporting into more than 10,000 pages while i am only selecting only 10 sheets.
Hello, Your code is doing great work on my worksheets but I'm facing one challenge with this code and the challenge is " As per the code Sheets(Array(2, 3)).Select in that manner i have to select the sheets counts. so, this is something manual work for me every time with increase or decrease in sheet count."
So, what can be done to this code if this automatically count sheets and merge them into single PDF.
Please help with this and share the code.
Thanks in advance !!
Hi Yash
Probably a bit late, but in case anyone else is asking the same question, you can use the command "MsgBox Application.Sheets.Count" . This will display the number of sheets in a messagebox, to implement it into your code would depend on your code, but you would want to maybe declare a variable as an integer, then set the variable to be the number of sheets, and then you are free to generate the pdf with this known number instead of having to change it each time! Hope this helps!
Hello Thank you for posting this video. Could youy help me to save multiple tabs but one in to a diefferent excel file??? I have try to declare that copy and save the selected sheets or sheets which names are "name of the sheet I don't want to save" but I haven't got the result that I'm looking for.
I have this video which will export all sheets as a separate Excel file - th-cam.com/video/zBFVm14biFI/w-d-xo.html
This is closer to what you need. Just now need to filter out the sheets you do not want.
I would have an IF statement run each time it loops round before the PDF export statement. That checks for the file name.
I don't know what your criteria is. But using the code in the video you would check if Worksheets(i).Name is equal or not equal to whatever criteria you are using.
The data in one of my sheets is three columns too wide to fit on one pdf page, how to I tell the program to extend that boundary to include my entire table? Thanks!!
just go to print visualization and change the bounds of the pages, you can also rotate the pages in the print options, run the macro again and u should be fine
I get a Run-time error 1004 "Document not saved" and the debug marks the "ActiveSheet.ExportAsFixedFormat..." as the error. Do you have a suggestion for a solution to this?
Not really Ditte. Try using F8 and stepping through the code. It might help identify the issue.
Is there a reason it would struggle to save. Are you saving to a directory with a mistype, or security preventing you.
Thanks , What if I need make multiple printing area in the same sheet and combine to one PDF ?
You can use multiple print areas in Excel by entering something like A1:C10,A15:F40 in the Print Area box. So I don't see why this kind of technique cannot be hard coded or recorded into a macro to export as PDF.
Yup, this works absolutely. However, it looks like a page break is inserted after each part of the range. So your example would result in pdf page 1 = A1:C10, pdf page 2 = A15:F40. Would you have any idea how to prevent page breaks? Thanks!!
@@janmirus The only way I was able to resolve the same issue was to paste the ranges into a temporary workbook and create the pdf from there
Help me
Error file:
Run-time error ‘75’
Path/file access error
It cannot create the directory/folder that you want. It may already exist.
@@Computergaga I managed to fix the error that only the hidden sheet could not export to the pdf file
Excellent. Well done!
Excelent Sir. This is very good and Theanks a lot but my some excel sheets very hidden This formula not save in hidden sheets please help me۔ how to create a VBA formula but save as PDF only hidden sheets.
Ah, I just shared this link with you on a separate comment, Saleem. Sorry for that. If this doesn't work for very hidden sheets, we would need to unhide them before exporting them. And I guess hide them again, if required.
You can unhide a sheet with the following code
Worksheets("Sheet1").Visible = xlSheetVisible
So, I haven't practised this, but maybe loop through the sheets and build an array of the very hidden sheets. Then combine into one PDF.
@@Computergaga ok sir
For some reason, only the first sheet is printing to pdf. All else works perfectly. Any Ideas?
Only to check the array and the sheet names are entered correctly.
i have been trying for hours now but I am continuously getting error 1004 "application-defined or object defined error". pls help
Sounds like it doesn't recognise an object you have written - so maybe a typo. Check through the code in the video. There will be something.
Hi ! Maybe you can help me with this. I'm trying to: loop through a folder of excel files, and export as PDF, sheets 2 to 5 of each file, the name of the PDF is located in Cells(1,1) of Sheet 1.
Sub export_PDF_Test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ruta = ThisWorkbook.Path & "\"
archivo = Dir(ruta & "*.xlsx")
RutaCompleta = ruta & archivo
Do While archivo ""
Set l2 = Workbooks.Open(RutaCompleta)
l2.Sheets(Array(2, 3, 4, 5)).Select
*** code: to export as PDF (exportasfixedformat) *** ???
l2.Close True
archivo = Dir()
Loop
I have tried many things but always get an error.
Thanks for the help!
Code to export as fixed format can be found at this link. What you got is close.
www.computergaga.com/blog/export-multiple-sheets-to-a-single-pdf-file-excel-vba/
Thanks!!
This is not exactly what I'm trying to do.
What I want to do is to loop through a folder of Excel Files an PDF all of them.
Thisworkbook is also in the same folder as the other files. I tried this:
********************
Sub export_PDF_Test()
' Thisworkbook.name = "Export PDF"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'Path
ruta = ThisWorkbook.Path & "\"
archivo = Dir(ruta & "*.xlsx")
RutaCompleta = ruta & archivo
'Loop through Folder
Do While archivo ""
Set l2 = Workbooks.Open(RutaCompleta)
l2.Activate
l2.Sheets(Array(2, 3, 4, 5)).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ruta & archivo
l2.Close True
Debug.Print archivo
archivo = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
********************
But is always creating a PDF of thisworkbook and not the other files.
Any idea?
I've been doing this for a while now. What I need is to print multiple tabs onto a single page pdf... ideas?
That is what the video shows.
Computergaga it shows exporting multiple tabs to multiple pages. I want to have two tabs on the same page
What if I want that the file name of the pdfs to be exported is from a specific cell?
No problem. Instead of the "\Sales" part you would reference the cell. How you do this depends where it is, and if it changes on each sheet etc. But if it is in cell A2 of each sheet you could write "\" & Range("A2").value
@@Computergaga whats if it is only in one sheet? Because for me its only changing in 1 sheet
Can someone help me, what if I have to export to PDF, all the worksheets within the excel workbook excluding the 1st Sheet.
Note: The number of worksheets may vary
After you make the folder (mine is called "myFolderPath") use the following loop to cycle through your sheets and check the name:
I = 0
Dim SheetNames()
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount - 1)
For Each myWorkSheet In Worksheets
If myWorkSheet.Name "Sheet1" Then
myWorkSheet.Activate
I = I + 1
SheetNames(I) = ActiveWorkbook.ActiveSheet.Name
End If
Next myWorkSheet
Sheets(SheetNames()).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFolderPath & "\sales", _
openafterpublish:=True, ignoreprintareas:=False
where can i get the developer ?
Right mouse click anywhere on the Ribbon, Click Customise the Ribbon, check the Developer box on the right.
Can you guide this in google sheet?
Sorry I don't use Google Sheets much.
@@Computergaga Okay, cool. No problem.
how i can use it by command button
and how to use it on hidden sheets also
You can create the command button by clicking the Developer tab, Insert and then select the command button from the list. Once drawn it should prompt you for a macro and you can select this one.
If you mean a command button on a userform then you will need to put this code in the click event of the button instead of a sub procedure.
These sheets would need to be unhidden first. They can always be hidden again in the code immediately after.
The code below can be used to check if a sheet is hidden and if so unhide it. This code checks all worksheets. Adapt as needed.
Dim i As Byte
For i = 1 To Worksheets.Count
If Worksheets(i).Visible = False Then
Worksheets(i).Visible = True
End If
Next
Sorry please check it whats wrong in it sir
Private Sub CommandButton1_Click()
Dim i As Byte
For i = 1 To Worksheets.Count
If Worksheets(i).Visible = False Then
Worksheets(i).Visible = True
End If
Dim FolderPath As String
FolderPath = "C:\Users\HAPPSS\Desktop\MonthlyTEOs"
MkDir FolderPath
Sheets(Array(2, 3, 4, 5, 6)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\TEOs", _
openafterpublish:=False, ignoreprintareas:=False
MsgBox "All TEO's Have been Successfully Exported to PDF File ."
End Sub
Try it with an extension on the end of the filename such as Filename:=FolderPath & "\TEOs.xlsx"
This macro does not work at all...
I would check the code. It definitely works.
Help me
It showing error
Run time error 75
Path/access file error
Please help
Sounds like an issue to where you are saving the PDF. Check the string for typos or missing "\"
@@Computergaga I have this issue, checked re-checked and changed everything I possibly could to make it work but nothing...