Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Printing multiple sheets to separate pdf

Hi,

I have a woorkbook (master.xls) with several worksheets. I am trying to make a macro that will print each worksheet as a separate pdf file (A pdf writer is my default printer), and name the files according to the text in cell A1.

Can someone please help me in writing suck a macro?

I found this code online but get te error message; "User-defined type not defined" in line Dimpdfjob as pdf creatonr.clsPDF creator.

Sub PrintToPDF_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String

    '/// Change the output file name here! ///
    sPDFName = Range("A1").Value
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

    Set pdfjob = New PDFCreator.clsPDFCreator

    With pdfjob
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"

    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
        DoEvents
    Loop
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub

Gnoke


Post your answer or comment

comments powered by Disqus
Hi all,

I was wondering how i would go about modifying the code i have below so that
i can print multiple sheets to a single PDF file with each worksheet printed
being a different tab. (if that last part isn't possible, then 1 pdf file
with each page being a separate sheet from the workbook.)

Note: my workbook has 15 sheets, only 1 sheet is visible at a time. the
print function will unhide/hide the sheets so that they can be printed with
the code below. (not all 15 sheets will be printed to pdf, only 4)

here is the code i am using to print to a pdf file:

If printToPDF And (pageID(i) = "Invoice" Or pageID(i) = "Withdrawls" Or
pageID(i) = "Deposits" Or pageID(i) = "Sales Journal" Or pageID(i) =
"Summary" Or pageID(i) = "Inventory Costs") Then
pb.Caption3 = "Printing" & pageID(i)
Set myPDF = New PdfDistiller
PSfilename = path & "temp.ps"
If pageID(i) = "Invoice" Then
PDFfilename = path & order & ".pdf"
Else
PDFfilename = path & pageID(i + 2) & " " & Year(Date) &
".pdf"
End If
ActiveWindow.SelectedSheets.PrintOut copies:=1,
ActivePrinter:=ChoosePrinter(pdfPrinter), preview:=False, printtofile:=True,
collate:=True, prtofilename:=PSfilename
pb.Caption3 = "Converting to PDF...."
myPDF.FileToPDF PSfilename, PDFfilename, ""

Kill PSfilename
On Error Resume Next
Kill path & "*.log"
On Error GoTo 0
Set myPDF = Nothing
End If

i have tried printing each sheet to a .ps file and then using
mypdf.filetopdf to convert it, but i haven't been able to get it to accept
multiple input names.

If you need my entire printdocuments sub, let me know and i'll paste that

tia!
J

Hi All,

I have the below macro which prints all excel sheets to pdf using pdfcreator as printer, it works absolutely fine but I want to come up with a userform where all excel sheets of a workbook will be listed down and then the user will be able to select the sheets and create the pdf as per his requirement. The Userform will do the following things for user.

1) Userform will allow user to select the sheets which he want to print to pdf.
2) It will allow him to select a option wherein he will able to print multiple sheets to one pdf.
3) If he doesn't select that option it will print each sheet to a separate pdf and save it on a selected path.

I have come up with a userform but I need a help to incorporate the below macro to the same so that they work as per above requirements.

' Print Multiple Worksheets to a Single PDF File:
Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_MultiSheetToOne_Early()


    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here! ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = New PDFCreator.clsPDFCreator

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Else
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
    Loop

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cCombineAll
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    MsgBox ("The PDF has been successfully created as " & sPDFName)
    pdfjob.cClose
    Sleep 1000
    Set pdfjob = Nothing
End Sub
Attached is my macro file.

Thanks a lot for your help in advance.

I have code to print out selected sheets from one workbook. Have it and it works great thanks to leahProton.

I now need to save those selected mulitple sheets as a single PDF file, so I found code to do that. (opver all problem is to spool all printing s a single job)

I can get each code to work separately, but not together.

I need some help, please!

Thanks, J

Here is my code to printout the selected sheets"
Sub report()
'----------------------------------------------------------------------------------------------------
'When this procedure is run, it will:
'    select the QUOTE sheet
'    count the number of "Items"
'    lookup the item number on the summary sheet and copy the corresponding sheet name to the printlist array
'* added:
'    it will check quote sheet row 33 for an entry, if true, then add the INDIVIDUAL PRODUCT sheet
'
'    it will add the QUOTE sheet as the last sheet in the list to be printed.
'------------------------------------------------------------------------------------------------------

     Dim PrintList()                                                       'array to hold sheets to print
     Dim SheetCount As Integer
     Dim ItemCount As Integer                                              'number of non-empty cells in Item column, i.e.,
A17 thru A29
     Dim TableRange                                                        'data on SUMMARY sheet
     Dim Item                                                              'current cell in list of QUOTE "Items"
     Dim ItemList                                                          'the "Item" column itself
     Set ItemList = Range("a17:a29")
     Set TableRange = Worksheets("Summary").Range("A7:G37")
     
     Worksheets("quote").Select
     ItemCount = Application.WorksheetFunction.CountA(Range("a17:a29"))    'get the total number of items listed
     
     ReDim PrintList(1 To ItemCount)                                       'size the sheet list array to hold this many sheet
names
     
'Process the "Items" column:
'for each item, check if it has a corresponding sheet name, then,
'if so, copy the sheet name to the print list

     For Each Item In ItemList
          If Item.Value > " " Then                                         '<=== note: changed the test value
from "" (NULL) to " " (space)
               SheetCount = SheetCount + 1
               If Application.WorksheetFunction.VLookup(Item.Value, TableRange, 7) <> "" Then
                    PrintList(SheetCount) = Application.WorksheetFunction.VLookup(Item.Value, TableRange, 7)
               Else
                    SheetCount = SheetCount - 1
               End If
          Else
               Exit For
          End If
     Next Item
     
'---------------- mod ------------------------------
'The following if..then..else block will check for entries in row 33 of the quote sheet.  If there are, then it will
'add the A. INDIVIDUAL PRODUCTS sheet to the list along with the QUOTE sheet; else it just adds the QUOTE sheet.
'---------------------------------------------------

     If Sheets("quote").Range("E33").Value > "0.00" Then
          SheetCount = SheetCount + 2
          ReDim Preserve PrintList(1 To SheetCount)
          PrintList(SheetCount - 1) = "A. INDIVIDUAL PRODUCTS"
          PrintList(SheetCount) = "QUOTE"
     Else
          SheetCount = SheetCount + 1
          ReDim Preserve PrintList(1 To SheetCount)
          PrintList(SheetCount) = "quote"
     End If
     
     Sheets(PrintList()).Select                             'select all sheets in the list
     ActiveWindow.SelectedSheets.PrintOut Copies:=1         'print the sheets
     Sheets("QUOTE").Select
End Sub

AND HERE IS THE CODE TO SAVE MULTIPLE SHEETS AS A SINGLE PDF FILE

  
Option Explicit

Sub PrintToPDF_MultiSheetToOne_Late()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for late bind, no references req'd

    Dim pdfjob As Object
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here!  ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
            vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Else
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
    Loop

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cCombineAll
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub


Greetings,

On some Excel worksheets when I try to print multiple tabs to a pdf file Adobe wants me to enter a separate file name for each page. On other Excel worksheets the multiple page pdf printing works fine.

What am I missing to be able to print the multiple tabs (pages) into a single pdf file?

Thanks,
phippsto

Hi All,

I have the below code which uses pdfcreator and prints multiple sheets into one pdf.

' Print a Single Worksheet to a PDF File:
Option Explicit

Sub PrintToPDF_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String

    '/// Change the output file name here! ///
    sPDFName = "testPDF.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

    Set pdfjob = New PDFCreator.clsPDFCreator

    With pdfjob
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
                    vbOKOnly, "PrtPDFCreator"
            Exit Sub
        End If
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"

    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
        DoEvents
    Loop
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub

' Print Multiple Worksheets to Multiple PDF Files:

Option Explicit

Sub PrintToPDF_MultiSheet_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long

    Set pdfjob = New PDFCreator.clsPDFCreator
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "PrtPDFCreator"
        Exit Sub
    End If

    For lSheet = 1 To ActiveWorkbook.Sheets.Count
        'Check if worksheet is empty and skip if so
        If Not IsEmpty(ActiveSheet.UsedRange) Then
            With pdfjob
                '/// Change the output file name here! ///
                sPDFName = "testPDF" & Sheets(lSheet).Name & ".pdf"
                .cOption("UseAutosave") = 1
                .cOption("UseAutosaveDirectory") = 1
                .cOption("AutosaveDirectory") = sPDFPath
                .cOption("AutosaveFilename") = sPDFName
                .cOption("AutosaveFormat") = 0    ' 0 = PDF
                .cClearCache
            End With
    
            'Print the document to PDF
            Worksheets(lSheet).PrintOut copies:=1, ActivePrinter:="¯PDFCreator"¯
    
            'Wait until the print job has entered the print queue
            Do Until pdfjob.cCountOfPrintjobs = 1
                DoEvents
            Loop
            pdfjob.cPrinterStop = False
    
            'Wait until PDF creator is finished then release the objects
            Do Until pdfjob.cCountOfPrintjobs = 0
                DoEvents
            Loop
        End If
    Next lSheet
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub

' Print Multiple Worksheets to a Single PDF File:


Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_MultiSheetToOne_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here! ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = New PDFCreator.clsPDFCreator

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Else
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
    Loop

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cCombineAll
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    MsgBox ("The PDF has been successfully created as " & sPDFName)
    pdfjob.cClose
    Sleep 1000
    Set pdfjob = Nothing
End Sub
But before printing I want to set the pagesetup for eachworksheet as below before printing.

1) Page : Legal
2) From Page 1 to 1

Thanks a lot for your help in advance.

I have print areas in 4 separate worksheets. Using Ctrl + select sheets, then
print to Adobe Acrobat results in 4 separate files. Adobe wants a new file
name at the outset of printing each area. Is there any way to print these
areas to one PDF file?

Hi everyone.

I am working on a userform in which user can select diffrent sheets for print.
I want all selected sheets to be printed in a single pdf and it could be great if it not ask for a path to save file.
Thanx in advance.

Hello everyone. I am trying to simplify a printing function in on one of my worksheets. It is a report that needs to be printed for multiple people. I am a beginner at VBA and the code below basically takes all the ID numbers from column "A" and pastes them into "M2" to print. What it does not do is print these pages to a PDF printer. Can anyone help me modify this code to be able to print each page to one PDF file?

Thanks for any help.

Sub Mailmerge_3()
Dim endrow As Long
If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub
Application.Dialogs(xlDialogPrinterSetup).Show
    endrow = Cells(Rows.Count, "A").End(xlUp).Row
 For i = 2 To endrow
    If Cells(i, 1).Value <> "" And Cells(i, 1).Value <> " " Then
         Range("M2").Value = Cells(i, 1).Value
         Sheets("Report").PrintOut Copies:=1, Collate:=True
   End If
 Next i
End Sub


Windows XP SP2
Excel 2007 SP2
PDFCreator 0.9.3

Hi

I have been using the Ken Puls' (ExcelGuru) code for creating a single PDF file from multiple worksheets found here:
http://www.excelguru.ca/node/21#MultiSingle

SUMMARY - Ken's code assumes that jobs added sequentially to the PDFCreator job queue are cumulative, however my problem is that old jobs drop out of the queue before new jobs are added.

I have posted a problematic excerpt of the code and the output.
'........
    Dim i As Integer
    Dim TimerStart As Long
    TimerStart = Timer
    
    Dim oldPrintCount As Integer
    oldPrintCount = pdfjob.cCountOfPrintjobs
    
    Debug.Print
    Debug.Print "lTtlSheets: " & lTtlSheets
    Debug.Print "Initial Printjob Count: " & oldPrintCount & " at " & Timer
    
    If oldPrintCount > 0 Then
                    
        For i = 1 To pdfjob.cCountOfPrintjobs
        
            Debug.Print pdfjob.cPrintjobFilename(i)
            
        Next i
    
    End If

    Debug.Print
    
    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets _
        Or (Timer - TimerStart > TimerInterval2 And pdfjob.cCountOfPrintjobs = 0)
        
        If oldPrintCount <> pdfjob.cCountOfPrintjobs Then
            Debug.Print "Previous Printjob Count: " & oldPrintCount
            Debug.Print "Current Printjob Count: " & pdfjob.cCountOfPrintjobs & " at " &
Timer
            Debug.Print
            oldPrintCount = pdfjob.cCountOfPrintjobs
            
            For i = 1 To pdfjob.cCountOfPrintjobs
            
                Debug.Print pdfjob.cPrintjobFilename(i)
                
            Next i
            
        End If
        
        DoEvents
        
    Loop
'........
So - this code executes once all the required worksheets (3 of them) have had the PrintOut method invoked. I've added some debugging code to illustrate my problem.

Output:
lTtlSheets: 3
Initial Printjob Count: 1 at 45197.67
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS11C.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45197.94

Previous Printjob Count: 0
Current Printjob Count: 1 at 45198.44
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS121.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45199.14

Previous Printjob Count: 0
Current Printjob Count: 1 at 45199.64
C:Documents and Settingspootle_flumpLocal SettingsTempPDFCreatorPDFCreatorSpool~PS126.tmp

Previous Printjob Count: 1
Current Printjob Count: 0 at 45200.41
As such, jobs appear to be getting added to the queue but then being processed before the rest of jobs are being added. The result is that the PDF created only contains one of the worksheets. I added the check against the Timer as part of the loop condition because my code was looping forever.

This is an intermittent problem - when I ran this the first couple of times getting the stuff together for this post it ran perfectly!

Any help would be gratefully received.

Thanks

Hi

I use Excel 2003. I also currently use PDF reDirect v2 (free version). I'm happy to use any other free PDF creator if necessary. If cost effective, I will also buy a full version of a PDF creator.

I have a spreadsheet containing about 95 sheets. The first 5 sheets contains calculations. Sheets 6 to 95 currently contains statements and are named 1001, 1002, 1003 up to 1090.

Please note that monthly new sheets might be added. For example, next month there may be new statements (sheets) named 1091, 1092 and 1093.

I would like to create a MACRO that:

1. prints sheet 1 (named 1001) to a pdf doc
2. names the pdf doc "1001"
3. saves the pdf doc to the same location every time (e.g. temp folder)
4. prints sheet 2 (named 1002) to a pdf doc
5. repeats steps 2 and 3 for sheet 2
6. repeats steps 1 to 3 for all the sheets up to the end of the spreadsheet.

Could anyone please assist? This would save me hours every month. Currently I have to create a pdf for each page manually.

I can post an "example spreadsheet" if required?

Thanks!

Bruwer

Quick question--

Trying to print multiple sheets, using naming convention "Sheet16, Sheet15, etc." However, getting type mismatch error when using the array. Here is code so far:


	VB:
	
 Print_Customer_Handouts() 
     '
     
     '
    Worksheets(Array(Sheet15, Sheet16, Sheet17, _ 
    Sheet18, Sheet19, Sheet20, Sheet21, _ 
    Sheet22, Sheet23, Sheet24, Sheet25, _ 
    Sheet26)).Select 
    Sheets(Sheet15).Activate 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
    Sheets(Sheet2).Select 
    Range("A4").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I need to use the Sheet## naming convention as others may move the sheets or rename them. Any ideas?

By the way, it seems sensible to me to always name sheets by the Excel permanent name. Am I missing something? Or is it because of the errors I get in the above example?

Thanks for your help!

Hello all and thanks in advance!!

I have a text only spreadsheet that is 70 rows high and 150 columns long. I'd like to have this printed as one long sheet as a poster/plot at a print shop. I have been trying to save my sheet as a PDF to give to the print shop (which is what they have asked for), but when I save it text disappears from the cells. I am using Excel Mac 2011. I currently have my settings as 18 pt font size, portrait, fit to one page wide and one page tall, and custom paper size of 42 inches wide and 20 inches tall.

Thanks for any help!!
Stacey

Hi there,

Ive been trying to find a macro that allows me to print certain sheet in a workbook to a single PDF using my "Adobe PDF" printer.

I've previously used a macro where if something exists in a certain cell, the sheet will print. This works but each sheet prints out in a seperate PDF.

The code ive used before is as follows:

Sub Macro2()
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
   If sht.Range("AJ1").Value <> "" Then
        sht.PrintOut
    End If
Next sht

End Sub
Can anyone help me please?

Matt

Hi guys,

Anybody have some code laying around I could look at for printing each sheet to a PDF named after the sheet, on workbook opening?

Want to automate a process, so it opens - updates from other sheets - prints all sheets to individual PDF's, then email each PDF to a specific inbox.

Cheers

My problem is my code will only print one sheet befor going to the next counter. I'm trying to get to print multiple sheet equal to the value of cell J80. Before going on to the nect counter. Example if cell J80=5 it should print 5 sheet as one print job and then move to next counter

here is my code

A Big thanks in advance


	VB:
	
 doprint() 
     '
     ' doprint Macro
     
     
    Dim i As Integer 
    Dim oCell As Range 
     
     
     
    strjobnumber = InputBox("Start in Job Number?", " First Job to 
    Print", 0) 
    endjobnumber = InputBox("Finish in Job Number?", " Last Job to 
    Print", 0) 
     
    Range("I40").Select 
    Range("I41").Select 
     
     
    For Counter = strjobnumber To endjobnumber 
        Sheets("Pieces").Activate 
        Range("L5").Value = Counter 
         
        ***Here I would Like To grabe the value of cell J80)*** 
         
        Sheets("BatchSheet1").Activate 
         
        ****Here I would Like tp print the number of sheet equal To 
        the value of Cell J80 As one print job*** 
        ****Currently it will only print one sheet befor going To Next 
        counter******* 
         
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, 
        Copies:=1, Collate _ 
        :=True 
    Next Counter 
    Sheets("Pieces").Activate 
    Range("$A$1").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to find a way to select and print multiple sheets based on a criteria or a list.

I have a large worksheet with many sheets. Each sheet falls into one of three categories, and I want to be able to automatically print all tabs in each category.

I have all of the sheets rolled up into a summary where I have access to all of the sheet name and print criteria if that is helpful.

Thanks.

Hi,

I need some help with the creation of a macro in Excel.

I have in my excel-file created a drop-down list that looks up different names/codes from a list (in another sheet) and when I select a name/code a sheet gets populated. After that I push a button that prints the sheet to a PDF-file (I am using PDF995) where I have to specify the filename and location. This works well as long as I don’t need to generate a lot of different PDF’s (then it gets time consuming).

The thing I would like to do now is to create a macro that prints all the different sheets that’s gets generated from the different names/codes that I now need to select and print “manually”.

To further illustrate what I mean:

The list might look like this:
Peter
Fredrik
Jimmy
Jenny
Cecilia

When I in my input-sheet select Peter the sheet I want to print to PDF gets populated. I want the macro to go through my list from Peter to Cecilia and generate different PDF-files for each name in the list. I also would like the files to get saved with the name specified in a cell in my sheet.

Hope you understand what I mean.

Thanks for any help in advance.

Hi there-

I want to print multiple sheets in array fashion so that the page numbers are correct on the print.

How can I change this so that my if statements will add to an array that I can print at once? (Again I'm looking for consecutive page #'s on the print but printing a number of workbooks that have a dynamic # of pages)

Code:
 
Sheets("INVOICE").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False
Sheets("INVOICE").Select
If wsLABORDetail.Range("A13").Value  "" Then
wsLABORDetail.Select
End If
If wsEXPENDetail.Range("A13").Value  "" Then
wsEXPENDetail.Select
End If
If wsFixed_Travell.Range("F9").Value  "" Then
wsFixed_Travell.Select
End If
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False
For example this recorded macro prints the array, but I have no way of adding a criteria for printing as my if statements would do (here I am not using ws variables since I just recorded in macro):
Code:
 
Sheets(Array("INVOICE", "LABOR_Detail", "EXPEN_Detail", "Fixed_Travel")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False


hey all..
i have sheet3 that has a print button...

what i want to do with the button, is print 3 pages to my PDF printer.. however i don't want 3 seporate documents.... i want all sheet 1 to be page 1, sheet 2 to be page 2 etc.

is this possible?

Hey everyone. I have a problem. I've searched the forum, and couldn't find an answer to my question, so I figured I'd go ahead and post it.

I need to print multiple sheets from a single workbook (that have appropriate print areas already set up). I want to assign this macro to a button, so that I can just click one button and get a copy of every sheet.

Each of the sheet names is unique, and stored in column A on a sheet titled LIST, since there are four sheets of stuff that don't need to print. Each of the uniquely-named sheets are hidden as well. What I need:

* Print all sheets, using the names on LIST in column A (A1 to the last one with data; the names are alphabetized from ADAM on down to WILLOW).
* Ask for confirmation 'Are you sure you want to print all saved sheets?'
** If yes, print all the sheets.
** If cancel, do nothing.

Any help would be appreciated. I can see how Id do everything but using the A column to define what names to print, I think.

Does anyone know how to print multiple sheets of a workbook with one print job?

Thank you

Hi,

I have a large spreadsheet that I would like to print 3 different sheets to
one file (on different sheets) other than the one I am in. Does anyone have
sample code that would do this.

Thanks in advance for any help.

Adella

Is there a macro written or a way to write a macro that will take certain excel sheets and combine them to create one PDF?

I have the excel add-in that allows a user to export either the entire workbook or a single sheet to a PDF file.

I need help trying to copy data from multiple sheets to one single sheet. I am pretty sure this is possible. The problem I am running into is that the number of sheets at any given time is dynamic. The numbering of the sheets is from 000 to 999 (they must be a three-digit code). The other issue I am having is I only want to copy the rows in each sheet that have an "x" in column "A". Also, the row in which the first "x" occurs can differ from sheet to sheet. I have attached a copy of the spreadsheet that indicates how the spreadsheet is layed out. Any help is appreciated.


No luck finding an answer? You could always try Google.