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
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
