Print Macro to .pdf...Multiple Pages to One .pdf

[LastTrain]

I have a print macro designed to copy/paste, then print. It repeats these steps over and over as it moves row by row down the document. It works great.

However, after printing paper copies I'd love to be able to re-do the print macro while printing the multiple pages to just one .pdf file. When I change my active printer to print to a .pdf file and run the macro, it works as expected. I am prompted to provide a new file name each time it is ready to print the subsequent row. That's fine, but again, I'd love for each item to be added as a new page to an existing .pdf (or one I designate on the first print sequence).

It doesn't seem like this should be possible, but I figured it can't hurt to ask.

Here are the first 3 iterations of the code, which continues 120-ish rows.

Sheets("Master").Select
Rows("274:274").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Master").Select
Rows("275:275").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Master").Select
Rows("276:276").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1

 

[Kamolga]

Instead of puting in on sheet1 I would send it to a new workbook, putting each copied row on a new sheet. When you make a pdf of an entire workbook, Excel makes a new page for each sheet.

I tested with rows 274 to 276 as in example and indeed got 3 pages.

Sub Topdf()
Application.ScreenUpdating = False
Dim i As Long
Dim wbFrom As Workbook
Set wbFrom = ThisWorkbook
Dim wbTo As Workbook
Set wbTo = Workbooks.Add
wbFrom.Activate
Sheets("Master").Select
    For i = [COLOR=#ff0000]274 [/COLOR]To [COLOR=#ff0000]276[/COLOR]
        Rows(i).Copy
        wbTo.Activate
            Sheets.Add After:=ActiveSheet
        ActiveSheet.Paste
        wbFrom.Activate
    Next i
    wbTo.Activate
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    'wbTo.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

Note that I put 274 to 276 as in example but you can make it from 274 to 400 if you wished.

 

[Kamolga]

The code above works if printer is set up on pdf I believe, the way to avoid that is to replace

[LEFT][COLOR=#333333][FONT=monospace]ActiveWorkbook.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
[/FONT][/COLOR][/LEFT]

by

 ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= "C:\Users\[COLOR=#ff0000]xxx\Desktop\Document274_276[/COLOR][COLOR=#7d2727][FONT=Consolas].[/FONT][/COLOR]pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= True