Printing Multiple Worksheets and Workbooks

I have around 8 workbooks each with about 30 worksheets in them.

Is there anyway i can create a new workbook with a sheet containing several buttons. When one of the buttons is pressed it will print out a number of specific sheets from the workbooks?

I need to print the same "Calculations" worksheet from several Workbooks. I have the code listed below to do this, but I don't know how to build the macro to achieve this. Do I need to copy this Macro into every workbook? Could someone please help?

Code:

Sub PrintAllSheets()
    Dim wb     As Workbook
    Dim ws     As Worksheet
    Const strSht As String = "calculation"
    For Each wb In Excel.Workbooks
        For Each ws In wb.Worksheets
            If ws.Name = strSht Then ws.PrintOut
        Next ws
    Next wb
End Sub

Is there a way to write a macro that will print out multiple workbooks contained in a common folder without having to open up each workbook individually?

Thanks in advance.

I am using Excel 2003 with Windows XP. I was wondering if there is a way to select multiple workbooks and print a particular worksheet (it has the same name in each workbook) from each one without having to actually open each workbook or print everything in each workbook.

For instance: I am looking at a folder that contains 27 workbooks. Each workbook has 8 worksheets. I want to print the one titled "Rec Gen" from each of the workbooks, but I don't want to have to open each workbook, select the worksheet then print.

Thanks for any help!

I was wondering if it was possible to create a macro that with a push of a button would print multiple pages from multiple workbooks. For example, when publishing financials statements, I print the same 53 files every month and I have to open each file individually to print it.

Hello,

I currently have a spreadsheet that has a total of 20 workbooks in all. Each workbook contains between 5 and 100 rows and between 20-40 columns. Each column is a different category that I have data on for that particular tool manufacturer. What I want to do is create a front workbook where I can do all my searching. I would like the user to impute data into a search cell, hit a macro button, and have the full row that data was in be shown to them. Depending on which criteria they search for, the value may be found at the start, middle or end of the row.

Also, like I said the number of columns differs between each workbook. I would also like it to print out the categories for that workbook so that the person inputing the data knows what all the different numbers are when they come up.

Each workbook is a different tool manufacturer that my company has a tool from so there should never be duplicate data. The problem is that after I finish the spreadsheet, I am turning it over to the floor workers and they will have the ability to add/change any information in any workbook. If a mistake is made and different workbooks have the same data, I would like it to print out the category and data from the first workbook, then print the category and data from the next workbook and so on. The category headers make up the first 2 rows of each workbook.

My knowledge of Visual basic is very limited so if you are kind enough to help me out, please try to explain everything clearly. I would appreciate any help anyone could give. Thank you very much

R. Sherman

edited to give locations of category headers.

I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.

I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.

I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.

Any help will be greatly appreciated.

Hi everyone.
I have a shared excel workbook. Data is stored in the "data" worksheet. There is a form where the user can select which data to print. A macro selects user's choice from "data" sheet, copy and arrange it in a certain sheet ("Print"), and then call the printer dialog. The problem is when several users try to print at the same time: different data is copied in the same sheet ("print"). My question is: which is the best way to simultaneously printing in shared workbooks (multiple users)? Create a temporary excel file? Have multiple sheets in the workbook, one for each user? Any suggestion welcome.

Hello all,
I have been having some serious issues trying to find a way to make
Excel print two worksheets at the same time (one is in portrait and the other
is in landscape). If the job travels through our print server to the
printer, it will come out as two print jobs (one in portrait and one in
landscape). If the job travels directly to the printer, the portrait and
landscape options will be valid, however the landscape side will have been
cropped to be 8.5" squared.
While this sounds like something I shouldn't be getting worked up over,
I am trying to duplex an Excel spreadsheet. Sheet1 is in portrait and Sheet2
is in landscape. If I print to through the print server, they come out as
two seperate pages. If I print directly to a printer, they come out as a
duplex document, one portrait and the other oriented in landscape but cropped
to be portrait (8.5 inches squared).
Here's the kicker! If I print to an Adobe PDF, it comes out just fine
(with page 1 as portrait and page 2 as landscape, no cropping). If I then
pass this job onto the printer, it prints fine. So I have determined that
the problem occurs when Excel is told to print to the printer itself.
However, trying 4 different models (3 HP LaserJets and one Lanier copy
machine), the same effects occur.
All Office 2003 updates have been downloaded from OfficeUpdate. I have
been able to reproduce this issue on any machine (I'm up to 5 PCs so far,
including Windows 2000 Pro SP3, SP4 and XP Pro SP2).

I am open to ANY suggestions. Anyone? Please? :-)

Thanks in advance.

Hello,
I have tried several different methods of printing worksheets to PDFs via VBA and I think I found the best. I however, need a small adjustment in the code I think.

The code runs without errors but it never opens the PDF nor finishes creating it. The PDF creator is in a waiting mode and it does not list the correct location. In my code you'll see I want it to save to the "X: drive" but it saves to the "C: drive" instead.

Can anyone help adjust the code? Thanks a ton for any help, I have been working on this project for about a year and this is the last thing I need to finish and can not get it.

Here is my code:
Code:

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 = "X:\Logbook\PES Receipt.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
    pdfjob.cClose
    Set pdfjob = Nothing
End Sub

Note this code is just as it appears on Ken's website except for the sPDFName location.