Excel Vba Print To PDF Multiple Sheets

Excel VBA Question How do I take the data in each row of a table and insert it into an excel form each row fills out the same form? I would also like all the forms to bebined into one large PDF as well. Im a little confused. If your aim is to produce a whole bunch of individual forms (which is easy btw) why also produce one monster PDF? If not individual forms why use Excel? Are individual users going to be editing contents in Excel? And by Excel form italic do you mean a real Form Control italic form or just a formatted Excel worksheet? Im going to assume the latter. If your real aim is to use your data to produce a large PDF containing a number of forms then you might want to instead consider using the MS Access Report tool. I haven used it in quite a while but it is easy to learn and quite straightforward in handling repetitive data. You can even leave your data in Excel and just to Access. No code required. Introduction to reports in Access s The PDF bit is easy too. Just print to PDF using something like Microsoft Print to PDF ( italic which should be built into your copy of Windows) italic or one of the many many (free!) PDF writers (DoPDF etc.) or finally - if you need really low-level print control and money is not an object - you could go with Adobe Actobat Pro and their API library. Note that everything above - from Access Reports through Printing - can be directly controlled thru Excel VBA if that what you need to do. However if you absolutely need to construct a bunch of individual worksheets and then PDF print them all at once you could do it like this We have some data And a (very basic) template form Ans some code Dim xlW As ' Our workbook code code Public Sub FillAndPrintForms() code ' Grab data out of sheet Data code ' and use it to build multiple forms code Dim xlSData As ' Data code Dim xlSTemplate As ' Template code Dim xlSForm As ' new sheet for new form code Dim SheetNames As New Collection ' list of our new sheets code Dim SheetName As String ' name of our newest sheet code Dim RNdata As Long ' Row Number in the Data sheet code Dim Team As String ' Data in column 1 code Dim Yr As Integer ' Numeric in Col 2 code Dim Number As Integer ' Numeric in Col 3 code code ' get Excel ready code code Set xlW = ActiveWorkbook code Set xlSData = (Data) code Set xlSTemplate = (Template) code RNdata = 2 ' Title in row 1 code code ' Make a dummy xlSForm. We will always add sheets to the right code code Set xlSForm = xlSTemplate code code ' get the contents of the first data cell code code Team = Trim((RNdata 1).Text) code code ' while the cell in col 1 is not blank code code While Team < code code ' build up sheetName from Team Yr & # code code Yr = (RNdata 2).Value code Number = (RNdata 3).Value code code ' E.G. West Coast 1987 1 code code SheetName = Team & & CStr(Yr) & & Format(Number ) code code after=xlSForm ' make a copy of Template code code ' Set and remember new sheetname code code Set xlSForm = ActiveSheet ' our new guy code = SheetName ' sets name code SheetName ' our collection for printing code code DoEvents ' catch up code code ' Shove data into new form. code ' Cheap and nasty here ... code ' cell positions should be constants in this code. code code (2 3).Value = Team code (3 3).Value = Yr code code (5 3).Value = Number code code ' Given & Family (Cols 5 & 4) code code (6 3).Value = (RNdata 5).Text _ code & & (RNdata 4).Text code code ' Games Played (Col 6) code code (7 3).Value = (RNdata 6).Text code code ' That's it. Get ready for new row (if any) code code RNdata = RNdata + 1 code Team = Trim((RNdata 1).Text) code code Wend code code ' Sheets are all created. Let's print them code code PrintSheets SheetNames code code ' and we're done code code MsgBox Yay vbExclamation Stuey RULES!! code code End Sub code code Private Sub PrintSheets(pSheetNames As Collection) code ' Select the first sheet (We will explode if there isn't one BTW) code ' Then add all the other sheets to our selection. code ' Then print code Dim FirstSheet As Boolean code Dim i As Integer code code FirstSheet = True ' needs a CHECK code code For i = 1 To code code ' TRUE sets the eheet active code ' FALSE ADDS the sheet to the selection code code (pSheetNames(i)).Select FirstSheet code code FirstSheet = False ' not any more code code Next i code code ' Ensure (PDF) printer by hand code code (xlDialogPrinterSetup).Show code code ' And print code code code code End Sub code And we get Which creates selects and prints the new forms like