Create multiple PDFs based on a list in Excel | Excel Off The Grid
HTML-код
- Опубликовано: 2 мар 2022
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Download the example file ★
exceloffthegrid.com/excel-cre...
★ 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 ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
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.
Thank you so much. This is gold. I was able to implement this at work following your instructions
Nice example! Thanks for demonstrating. Thumbs up!!
Thanks for the support Wayne 😀
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
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. 😀
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.
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👍
Exactly what I was looking for!
Great stuff.
Wow Mark. I actually understood this. Thank you. You explain that very well. Now I need to experiment.
Glad it was helpful!
Excellent solution Mark. Great code development.
Thanks, I’m glad you liked it. 😀
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.
Very helpful, tnx tnx mark
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?
Hello. Thank you very much. Clear and useful.
Glad it was helpful!
Thank you! I used a modified version of this code to exactly get what I was looking for.
Great news 😀
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
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?
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.
Thanks a lot!
You're welcome!
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.
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 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.
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
Hi how do you reference the filename if i want to have data such as ID, Name and Date as the filename?
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 👍
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.
Excellant
Thank you! Cheers!
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
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?
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. ❤❤❤
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,
When i am trying tom run this macro the excel file closes automatically
One blank ID is generated during the running VBA, How to resolve it ??
Cool.
Thanks 😀
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
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
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, 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'
How to send it to outlook
How to work out the validation list
what if we use some photo for the data?
Sorry, I'm not sure if the use case. Can you provide some more information.
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