VBA Print in PDF

jlwag

New Member
Joined
Feb 18, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have this excel data base file where I have built a form on another tab that automatically fills in when I put in a number (it's the record number). The number represents a row of information. So whatever number I put in, it fills out the form and I can print that form. So then I built a VBA code that would put in all the numbers in sequence and prints outs all the pages with one command button.
What I need now is instead of printing the forms, I would like them to be PDFs sent to a particular folder, then I can look at each one individually. I would also like to make them all PDFs and combine them into one PDF and just print the one document out. The hard part is making them all PDFs.

Here is the formula I have to print all of the records.

Sub PrintAll()
RowCount = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 1

Worksheets("Report").Select
For i = 1 To RowCount
Range("B1").Value = i
ActiveSheet.PrintOut Copies:=1
Next i
End Sub

I also can print just one record. I have it twice for different worksheets below:

Sub PrintOneDropCard()
' PrintOneDropCard Macro
'
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub

Sub PrintOneMSEL()
' PrintOneMSEL Macro
'
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub


Can someone help me turn them in to PDFs. I tried doing it but it would only make one PDF and kept over writing each PDF and I ended up with only the last form and one PDF. I just can't figure it out. I can send the file to anyone who would love to try and figure it out.

I"m not very good at coding.

Thanks
James
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel

Try this:

Change "C:\trabajo\" for your folder.

VBA Code:
Sub PrintAll()
  Dim RowCount As Long, i As Long, sPath As String
 
  sPath = "C:\trabajo\"
  RowCount = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 1
 
  Worksheets("Report").Select
  For i = 1 To RowCount
    Range("B1").Value = i
    ActiveSheet.PrintOut Copies:=1
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=sPath & Range("B1").Value & ".pdf", _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=False
 
  Next i
End Sub
 

jlwag

New Member
Joined
Feb 18, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
That's so cool. Thank you very much. It worked like a charm. I did remove the line "ActiveSheet.PrintOut Copies:=1" so it wouldn't print out the documents too.

Anyway THANK YOU!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,134
Messages
5,622,930
Members
415,941
Latest member
georgiana686

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top