98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Macro print to PDF
Macro print to PDF
Resolved · Low Priority · Version 2007
Fri 7 Jun 2013, 11:34Delegate Matt said...
Matt has attended:
Access Introduction course
Excel VBA Introduction course
Macro print to PDF
Hi,
I'm trying to run a macro to print 17 excel tabs to PDFs and save. Ideally I want to reference a cell in each tab to provide the name for each PDF.
I'm a real VBA beginner so I've just recorded this and it seems to work except it's asking me to save the PDF each time.
I've been on a couple of forum posts which suggest this might not be as simple as it sounds but I'm sure I've seen it done.
Any help welcome.
Thanks,
Matt
Sun 9 Jun 2013, 21:28Trainer Gary said...
RE: Macro print to PDF
Hi Matt
Thanks for getting in touch. You need to take your code snippet and put it into a loop in order that you can change its value each time.
Assuming your file names start in A1 on Sheet1:
Sub SaveAsPDF()
Dim MyFileName As String
Dim i As Integer
Dim MaxRows As Integer
MaxRows = Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
For i = 1 To MaxRows
MyFileName = Sheets("Sheet1").Cells(i, 1).Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & MyFileName & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
Next i
End Sub
Modify as appropriate for your data. Let us know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Sun 16 Jun 2013: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Turn Function tooltips on and offExcel 2002 (XP) and Excel 2003 have the Function tooltips facility. When you type in a function name followed by a bracket, for example, =IF(, a yellow box appears beside the function name and lists the function's arguments. This is very useful when you can't quite remember the order of a function's arguments or what the arguments actually are! |
