Solved

Excel worksheets consolidated for printing

Posted on 2011-03-16
3
319 Views
Last Modified: 2012-05-11
I'm creating a punch list in excel with each worksheet representing a different room in an apartment. When finished  I would like to do is create pdf of the consolidated worksheets. But I would also like that consolidation to be only the worksheets that contain entries in the punch list items column that exists on each worksheet. I'm trying to avoid a pdf that contains empty worksheets.
 Would it be easier to have a button that would allow me to select which sheets I want? Could the code run through each worksheet for a specific range, and if the cells in that range contain any data, add that worksheet to the pdf? I have a couple of driver options for printing to pdf.
0
Comment
Question by:wlamore
  • 3
3 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 35149237
printing to pdf is best done using a regular "print-to-pdf" driver, any will do.

Easiest way to do this is to browse through all worksheets, determine for each one wether or not it is to be printed, and then print them out.
This is not a very difficult thing to do in a macro !
Best way would be to assign this macro to a key combination so that you can start the macro just by pressing some keys.
0
 
LVL 19

Accepted Solution

by:
akoster earned 250 total points
ID: 35149345
Public Sub print_to_pdf()

Dim ws As Worksheet
Dim to_be_printed()

    ReDim to_be_printed(1)
    
    '-- loop through worksheets
    For Each ws In Worksheets
    
        '-- if more than 1 rows are used, worksheet is considered filled with data
        If ws.UsedRange.Rows.Count > 1 Then
            ReDim Preserve to_be_printed(UBound(to_be_printed) + 1)
            to_be_printed(UBound(to_be_printed) - 1) = ws.Name
        End If
    
    Next ws
    ReDim Preserve to_be_printed(UBound(to_be_printed) - 1)
    
    '-- print out the sheets that need to be printed
    sheets(to_be_printed).PrintOut
    
End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:akoster
ID: 35149356
Best would be to also add
option base 1

Open in new window


as the first line of the module containing the macro
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an explanation of a simple data model to help parse a JSON feed
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now