Create multiple PDFs based on a list in Excel | Excel Off The Grid
ฝัง
- เผยแพร่เมื่อ 21 ส.ค. 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegri...
★ Download the example file ★
exceloffthegri...
★ About this video ★
I was on a call recently where somebody asked if it was possible to create a unique PDF for each item in a list. The answer is "Yes", so in this video, I want to show you how to do exactly that; how to create multiple PDFs based on a list in Excel.
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegri...
★ Where to find Excel Off The Grid ★
Blog: exceloffthegri...
Twitter: / exceloffthegrid
#MsExcel #VBA
Hi Mark. Thank you very much. Clear and to the point. I can see many uses for this. I appreciate the effort you put into helping us all up-level our skills!
Thanks Jim - hopefully you can put it to good use.
Hello friend I'm happy that your tutorial worked for me. If you could upload tutorial of the same style but instead of saving pdf I would like to send documents direct to the printer
This week I spent 2 days populating some confidential information from an Excel document to a Word document. This method is really the easy way, I had just to recreate the word model into Excel and easy to print 43 documents. Amazing, thanks Mark !
Good work 👍
You could also use field codes to link to Excel from Word. Then create the PDF from Word. That is covered in our training… so you will get to it soon.
Very useful example!!! VBA step by step in a simple way
Thanks Damian - It’s not always easy to make VBA accessible to non-coders. So I appreciate your comments. 😀
Wow Mark. I actually understood this. Thank you. You explain that very well. Now I need to experiment.
Glad it was helpful!
Exactly what I was looking for!
Great stuff.
Nice example! Thanks for demonstrating. Thumbs up!!
Thanks for the support Wayne 😀
Thanks Mark - I worked along with you and it saved the pdfs first time - no bother! I just might make more of an effort to learn VBA ( just a little anyway)
VBA is useful when you get to grips with it.... it just takes a bit of time (and a lot of practice) to get to grips with it.
Excellent solution Mark. Great code development.
Thanks, I’m glad you liked it. 😀
Thank you very much for all these valuable information you shared with your followers... i always see how much you are going to the point efficiently ... much appreciated and keep going and we are support you Mark.
Respectfully
Thanks, that’s very nice of you to say👍
Thank you so much. This is gold. I was able to implement this at work following your instructions
Thank you! I used a modified version of this code to exactly get what I was looking for.
Great news 😀
EDIT: I got it to work now, thanks!
Hi Mark, Great tutorial! I followed your steps but nothing happens when I run the macro. I don't think I messed up any of the code. Is there some sort of setting that I'm missing?
Thanks in advance!
Thanks for letting me know that you got it working. Good work 👍
Hello. Thank you very much. Clear and useful.
Glad it was helpful!
Very helpful, tnx tnx mark
Thanks a lot!
You're welcome!
Hi Mark,, wonderful job,,,
Can you please explain how to save bunch of pages in single file rather each page in single file.
There are some code examples in here that show how to do that:
exceloffthegrid.com/vba-code-save-excel-file-as-pdf/
did you find the answer please? I need to save the pages in one PDF as well
Great prasentation Mark! You could avoid spelling mistakes in variable names, by letting the editor do the writing for you.: once you declared a variable just write the first letters and then press CTRL + Space and the VBA Editor will insert the rest for you :)
Yeah, I know….
But, I get typing and having to think about other things like that just distracts me.
Years of bad habits, that now I can’t get out of 😂
@@ExcelOffTheGrid 😅
Hi Mark! Thank you so much. and how can i do if i want all page in to a single pdf file by save as from vba?
There are some other code snippets in this post:
exceloffthegrid.com/vba-code-save-excel-file-as-pdf/
Point 2 of the post should provide you with the relevant code adjustments.
This is sooo helpful! Thank you! Btw, what if my list is on the other tab, what do I need to do? Should I set it as well?
Super useful ! How can you create the pdf though without the list of student on the right. Can you only pick up a specific part of the worksheet for creating the pdf? Thanks in advance :)
Yes you can! Just change the print area for the workbook to exclude the list of students.
Hi Mark, thank you very much for these instructions. They have been very useful for my daily tasks at work. Just one question, I have a list with the different inputs I need, and I'd like - by using the data validation - to only print, for example, one of those registers. But when I run the macro, it creates all the PDFs using the information in the list, althought I only validated one cell. How can I choose to print only the ones that I want, and not the entire list, without deleting the other rows?
Don't know why am I getting a subscript out of range error at -
Set ws = ActiveWorkbook.Sheets(" ")
I have already declared ws variable.
What am I doing wrong?
Also what should I write in between the quotes? the excel file name or worksheet name?
Please help.
‘Subscript out of range’ means VBA can’t find something that you’ve told it to look for.
You’re looking for a sheet a single space for the name, which it can’t find. Put the sheet name in the double quotes and the error should disappear.
Hi Mark, thank you for this video. I’ve been able to implement into my work. However, I was going to ask how I can change my file name to a different cell rather than the rngID. Thank you in advance for your help.
Hello, Thank you for your video, it was very useful. I used it to produce invoices. It work perfect. Please could you help me to change this code to insert Month in the name of the file as well as client ID, based on cell on the invoice. Thank you.
Hello. Anyone who know’s how to change the file name reference which will be based on a specific cell value on the worksheet? Would appreciate your help. ❤❤❤
thanks mark - i tried adapting the code to pick from two criteria instead of one reference point, do you have an examples that could help with this
Hi Mark, what if in de ID list of names are blank cells. what do you need to type to skip the blank cells and go to the next one, where there is no blank cell i tried to make one but i can only get max 1 blank cell and after that it stops. so if there are multiple blanc cells i cant get it done
Dim i As Integer
For i = 3 To 2000
A = sheet1Tab.Cells(i, 1).Value
If A = "" Then i = i + 1 (this is where i need to skip multiple blanc places so i cant print a pdf but if i put 2 there it can skip a pdf that is not printed, how should i make this so it goes to the next row where there is something and not blanc)
A = ThisWorkbook.Sheets("sheet1").Cells(i, 1).Value
If A = "" Then Exit For
Hi , instead of creating by pdf how can the copies you make be in excel format (ie alternative to WS:ExportAsFixedFormat) which will alway create a pdf document vs an excel version document
How to work out the validation list
Hi i have 2 queries,
1) list is in a different sheet
2) i want cell value of a specific cell in the table to be the filename....
Can you please help me make this work
I try to use the Print .PDF code that you show in VBA even a simple approach (ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\testfileprint.pdf") but it won't work. I'm wondering if this an issue with MS 365 Excel. I can print .PDF manually from MS Excel, but for some reason VBA to .PDF the sheet does not work. I wonder if anyone else has this issue. Any resolution?
Excellant
Thank you! Cheers!
Hi how do you reference the filename if i want to have data such as ID, Name and Date as the filename?
How can you encrypt each pdf with a password
You'll need to get some 3rd party software. As Excel doesn't have the features to do that.
Thank you so much, I was able to work on my project. No. 1 fan
How to send it to outlook
Hi. Thanks. But is any code for appending these files in one pdf file. Thanks a lot.
Only if you have a other software installed such as Adobe Acrobat (full version).
did you find the answer please? I need to save the pages in one PDF as well
One blank ID is generated during the running VBA, How to resolve it ??
Hi Mark,
When i am trying tom run this macro the excel file closes automatically
Hi, could please help,
I typed the code step by step and made changes where necessary but it didn't save
I have my data validation on sheet two named 'reportsheet' and the list is on sheet 1 named 'Data'
Cool.
Thanks 😀
what if we use some photo for the data?
Sorry, I'm not sure if the use case. Can you provide some more information.