Printing Certain Rows In A Range From Several Worksheets

Jan 10, 2010

I have a workbook containing several sheets. New worksheets may be added. From each sheet, I would like to print the first row in range (AB1:AE200) along with any rows below the first that contains the value “Red” in column AB. From what I have been reading, it would seem that a temporary worksheet would be the answer using a copy/paste.

As each sheet would likely contain only three or four rows to then print, is there a way then to get all the data onto a single page, thereby preventing the need to print a single page for each sheet?

View 9 Replies


Printing Worksheets From A Listbox Selection?

Jul 28, 2014

What I'm trying to do is print only the selected worksheets from a listbox. I have created a UserForm and a ListBox which displays all unhidden worksheets in a workbook. The ListBox MultiSelect control is set to 1-fmMultiSelectMulti. I would like to be able to click CommandButton1 and send the selected worksheets to print, but going to Print Preview first. This is what I have so far:

Private Sub UserForm_Initialize()
'Displays only visible (non-hidden) worksheets in listbox2
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets


how I can modify this code to print just the selected worksheets?

View 6 Replies View Related

Printing One Worksheet From Multiple Worksheets

Jul 26, 2009

I have a bunch of workbooks i need to print from a particular sheet each time, which is always called 'calculation'.

View 8 Replies View Related

Worksheets Individually Numbered When Printing

Aug 6, 2013

i have a program that exports multiple invoices to an excel template for ease of formatting and printing. it can export multiple invoices at once, each invoice being on a separate worksheet. i need for the user to be able to print all worksheets at once and have the pages NOT be numbered 1-30, rather 1-2, 1-4, 1-3, etc.

i understand i can use the header to insert page numbers, but they only function the way i would like when you print each worksheet one at a time. however, there could be any number of invoices to print at once. the only drawback is i am unable to use macros due to security risks/settings.

i would imagine there is some concoction of formulas i could possibly use to accomplish what i need, as i know how many rows of data will fit before excel inserts a page break(56 rows of invoice items, and there are 18 rows besides that repeat on every page). so if i could come up with a way to tell excel when to increment the page number in a cell using a formula of some kind, that would be perfect. or, another way of setting up my template so that it will print page numbers as expected.

View 6 Replies View Related

Viewing And Printing Selected Worksheets - VBA

Sep 3, 2009

I have a workbook containing a number of spreadsheets. Some of the spreadsheets are user inputs. The results of the user inputs drive a number of final reports. The final reports (i.e. spreadsheets) are hidden from the user (I don't want the user to be overwhelmed with so many tabs when they open the excel spreadsheet).

I created on the main input tab spreadsheet the following:

1. Check boxes - so that user can select after making his/her inputs the reports that he/she wants to view or print.

Say there are 4 reports (call them Sheet1, Sheet2, Sheet3, Sheet4 - therefore, 4 check boxes. Through the Format Control, the checkboxes have cell links that yield TRUE (if selected) or FALSE if not selected - linked to cells A1, A2, A3, A4 respectively.

2. Option buttons - one for view and another one for print. Through the Format Control, the View and Print option buttons have cell links to cell A5 yielding 1 for View and 2 for Print.

3. Command button - that will clear the check boxes

I would like to know if there is a way to code in VBA to:

1. Unhide the spreadsheets corresponding to the check boxes if selected;

2. Print the spreadsheets corresponding to the check boxes if selected for printing; and

3. Clear the checked boxes to unchecked if the Command button is clicked.

I'm struggling with coding to perform the above tasks.

View 9 Replies View Related

Disable Printing In Excel Workbook That Has 3 Worksheets?

Jul 15, 2014

I am trying to disable printing in an Excel workbook that has 3 worksheets. I do want any worksheets to be able to print.

View 2 Replies View Related

VBA Code For Printing Worksheets That Are Hidden From A Selection

Jan 19, 2013

I have a workbook with various pages that are all hidden except the main page, on the main page it allows users to select items froms drop down boxes that returns a figure to cell B7 on the selection page.

What i would like to do is press a command button and the hidden worksheet that relates to that figure in cell B7 opens which allows the users to print it then after printing or closing the workbook is hidden again.

View 4 Replies View Related

Excel 2010 :: Printing Multiple Worksheets And Changing Printer Defaults

Jul 29, 2011

We have just refreshed our entire printer fleet to Xerox printers and as part of the project we have selected all defaults to B&W and duplex.

On a multi sheet excel file, If we click print then choose entire workbook and then change the print options to colour, it prints the only first worksheet in colour, and all the others in mono. i have read that excel treats each worksheet separately and does not cascade the changes made made in the first worksheet even if all sheets have been selected.

How can I get them all to print in colour?

We can set up another printer and change the defauts to colour but we dont want to go down that road and have 2 printers installed for each model.

Is there any code out there can will/ can change the properties of the first worksheet and cascade this throughout the workbook.

We use XP and W7 workstations and office 2003, 2007 and 2010. By the end of the year all workstations will be W7 and Office 2010.

View 2 Replies View Related

Printing Grouped Rows

Aug 11, 2013

I have a few pages of information and have grouped rows collapsed to show only the summary information. When I try to print only visible summary rows, whether I use print, print selection, copy and paste to another sheet or copy and "paste special" values to another sheet, all the hidden/grouped rows print or the summary rows print separately on different pages. Is it possible to print only the summary rows.

View 3 Replies View Related

Printing Only Unhidden Rows?

Jul 23, 2014

I have a worksheet that has rows of information up to row 3000. Sometimes all the rows are shown and sometimes a lot of them are hidden. When I try and print, the rows with info are printed but then the hidden rows are included as just blank pages. Is there a way I can get excel to print only the rows in 1-3000 that are shown and not hidden?

View 1 Replies View Related

Hide Rows Before Printing

Sep 6, 2007

I've a worksheet that contains a whole list of items in stock.

For example
Item Quantity
hot water bottle
cooking oils
breakfast oats

The above is an example of what might be seen in the spreadsheet. What i'd like to be able to do is before printing it out, i want items with 0 quantity to be shown only. So i decided to hide the rows that have items with no quantity. To do this, all i could think of is to have a button that may contain codes to hide the rows. The problem with that is the button will appear there in the printout. Is there any way of making the rows hidden before printing without using a button to trigger the code?

View 14 Replies View Related

Unhide Rows When Printing

Nov 28, 2007

Is there a VB routine that can unhide rows when I print?

View 9 Replies View Related

Removing Rows For Printing

Mar 10, 2006

I have a report that pulls data from another worksheet in Range A26:J58.
What I want to be able to do is assign a button for printing the report but
before printing from A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26 down so it is not a
random fill.

Also, I want to save this workbook as a template so that it can be used over
again, so I guess it would not be good to delete the rows in the range
otherwise I would have to recreate them.

View 14 Replies View Related

Printing Some Rows In Every Page

Nov 1, 2009

I am making a project in which there is vast data of around 10-15 pages to get print...but due to vast data its not possible to get all printed in 1 pages...So i want that cell A1:K4 & A47:K53 to get print in every sheet...Header and footer is not posisble due to some logo at the end of the there anyway for such printing option...

View 9 Replies View Related

Prevent Certain Rows Printing

Jan 9, 2007

By adapting a worksheet macro obtained previously via Ozgrid, I built a workbook that dynamically produces a database, in a worksheet titled “NumLtrWBS DB”, based on all possible combinations of what is listed in column A of three 3 worksheets titled: “ Num”, “Ltr” and “WBS, respectively.

The macro, named “NumLtrWBS", works wonderfully, until I add a row at the top of each list to contain a column header. The unchanged macro includes the column headers in the “produced” database. When I change the macro by altering the variable definitions to begin at A2 rather than A1, it still includes the column headers in the “produced” database. How should I change the macro so it doesn’t “produce” the column headers in the database worksheet?

View 3 Replies View Related

Hidden Rows Are Printing

Jan 18, 2007

I'm completely at a loss I have a worksheet with hidden rows which I do not want to print yet Excel prints them anyway resulting in 12 pages rather than just the pages I want.

View 7 Replies View Related

Printing Select Rows Only

Dec 12, 2007

I want to be able to print individual rows from my sheet. When I try, they run onto a number of pages. The information needs to be printed along with the column headers to make sense. Is there a way I can do this and get the rows to 'wrap' to save paper?

View 9 Replies View Related

Prevent Empty Rows From Printing

Nov 10, 2005

I have formulae in cells to reference another sheet, but if the referenced cell is blank, then the new cell I made blank (using the IF function). The problem is, when I want to print, Excel wants to print all of the cells with entries in them - even the ones with formulae in them that are blank.

Is there any way to prevent the empty cells from printing?

View 11 Replies View Related

Printing Columns And Rows In Every Page

Jan 26, 2009

in my file i have rows/columns a1:z50. now my requirement is that rows as header a1:a3, as footer a47:a50 and columns a:c to be printed in every page.

View 9 Replies View Related

Code For Printing Particular Rows And Columns

Jun 27, 2006

I have a worksheet that I use to track my clients and their meal selections over a five day week. Col A is their name, B is their shift (am or pm) C is their table number, D is whether they will attend that day (yes or no) and E is their meal choice (a b or c) Columns D & E repeat the same information for each day Mon-Fri.

I would like to have a print button so that on a daily basis I can print the clients name and thier meal selection (only those those who have a yes for that day) separating AM shift from PM and then grouping them by their table number rather than alphabetical order.

View 2 Replies View Related

Stop Blank Rows Printing

Dec 1, 2006

I tried to reduce the size of the printing area by deleting the blank rows. To achieve this I entered VBA code that appeared in the newsletter issue 3 after slightly modifying the code suiting to my need. But, while exeucting the code with all the parameters, xlCellTypeBlanks, xlCellTypeFormulas,xlErrors, the error as shown as a screen shot herein is populated. But, with the parameter,""xlCellTypeBlanks"" all the Blank Rows only got deleted and similarly only those rows containing Formulas are deleted, while the parameter is""xlCellTypeFormulas".

I want all the cells that contain xlFormulas, xlErrors and xlCellTypeBlanks as well be removed while executing the code given herein. But, if the range contains "xlFormulas" and "xlCell TypeBlanks", the rows containing blank cells are not removed and vice versa.

View 2 Replies View Related

Printing Variable Rows With A Macro

Feb 14, 2007

I export data from another programme into excel and have written a macro to move and format this. The number of rows of data exported will be different each time. I need to be able to print this data and want to add it to the macro. I have learnt to use this code to select this data working from the last used cell to the top

Range("a1:d1", Range("a65536:d65536").End(xlUp)).Select

Using the macro recorder for printing one of my exports I got this code

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup.......................

View 5 Replies View Related

Printing Only Rows With Non-blank Cells

Mar 3, 2007

I am trying to print only the non-empty rows in my sheet ("1"), so I am checking every row from row 1 to row 500 to determine if that row is empty, in that case it will be hidden temporarily, then I will PrintPreview only non hidden rows. Finally, I will unhide all rows.

The problem is that although the following code works, it takes forever to run due to large number of rows. Based on sheet design, once the first empty row is discovered, all the following rows beneath it are all blank by default, so all I really need to do is automatically hide all the rows following the first discovered blank one without checking them, then proceed with PrintPreview as above. Sometimes my 3rd row is blank, so I could save a lot of time by automatically hiding rows 3 to 500 without checking them.

code below:

Sub Hide_Print_Unhide()
Dim rw As Long
Application. ScreenUpdating = False

With Sheets("1")
For rw = 1 To 500
If Application.WorksheetFunction. CountA( _
. Cells(rw, 1).Range("A1:C1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A1:A500").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

View 7 Replies View Related

Auto-fit Rows & Column For Printing

Sep 14, 2007

I've got a report that is filled out every day and submitted to the VIP's within the company.

They normally just open the file, press print and read the hard-copy.

Quite often the text of the column is larger than the cell size and the text is cut off.

View 7 Replies View Related

Move Rows Up In Sheet And Skip Printing?

Jul 17, 2013

I'd like to know if is possible and how to move up rows that I change a value for example the column time has a row with value 02:00 and I change the value to 01:00 and the row moves up

Also when I change the value to 00:00 how to move up the row and when printing skip these rows.

View 1 Replies View Related

Create A Report Printing Rows By The Content Of A Column?

Apr 14, 2014

In the column marked SA I have some numbers. These are Sub-Accounts. I am looking to run off a report that prints off all rows with the respective SA number. I have only included a "4" and a "5" in my example but there is going to be more than one row "assigned" to Sub Account 4, Sub Account 5 etc. Ultimately it provides a break down of the accounts.

View 7 Replies View Related

Automatically Set Print Range For Last Row Before Printing

Feb 19, 2008

I'd like to set the print range based on the last row with text in specific columns. I found a couple of macros in this forum to adapt, but neither are working. Extra rows, which only contain conditional formatting, and other excluded rows and columns still print.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim MaxRow As Long, i As Integer
MaxRow = 1
For i = 1 To 6
Cells(65536, i).End(xlUp).Select
MaxRow = Application.WorksheetFunction.Max(MaxRow, ActiveCell.Row)
Next i
ActiveSheet.PageSetup.PrintArea = "$A$2:$F$" & MaxRow
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim LastRow As Long
LastRow = ActiveSheet.Columns("A:F"). Find(What:="*" _
, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.PageSetup.PrintArea = "$A$2:$F$" & LastRow
End Sub

View 4 Replies View Related

Printing Multiple Workbooks With Printing Preference As Landscape

Jan 15, 2012

I would like to know if there is a way to print several different workbooks at once but keeping my printing format which I would like to be Landscape and Fit to one page. Reason is simple as I work in a office where staff is handed in several jobs to do everyday. They finish the jobs and log all the info on the database. I log on to database and put all their daily diaries and because it is all over the place I have to go in each file and set printing preferences which takes an hour in the morning and hour in the evening. I could do with some sort of automation where all diaries are automatically printed in Landscape and Fit on one page.

View 2 Replies View Related

Printing / Not Printing Ranges Based On Cell Contents

Apr 11, 2008

I have a report that is generated from a manufacturing process that looks like the example below. the report is 40 pages long when all the data is printed. i am looking for a way to only print this range if a dimension is "out of tolerance". if the dimension is within tolerance, there is always the "garbage" text of plus and minus. if every row is "within tolerance" in the range the cells in the OutTol column would all contain the "garbage" text but it will not always be identical. so, in summary, actual OutTol values = print and all "garbage" = not printed.


View 9 Replies View Related

Excel 2013 :: Printing A Range Of Cells?

Jun 11, 2014

I am using Excel 2013

I need to print to a printer that is not the default printer, a range of Cells e.g. B4:L28 on Sheet Print Out.

The code is to be added to a VBA routine that already exists that collects & arranges the data on the Print Out sheet from other sheets in the workbook. This routine is assigned to a button on another sheet.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved