Macro: Print Sheets If Used

Sep 9, 2006

I'm in the process of placing a button on sheet#1 that will print any of the 5 sheets in the workbook that have been used... a sheets use is determined by cell I53 being > 0...

I've initially used this code to test I53, but it shows all sheets, regardless of I53 value...


Sub PrntUsedShts()
Dim ws As Worksheet

For Each ws In Worksheets
If Range("I53").Value > 0 Then
ws.PrintPreview
End If
Next ws
End Sub




Ideally, the finished code will send all selected sheets collated as a single print job...
Should I be looking at putting test positive sheets into a sheet array?, then printing the sheet array?

Once again, your valued input is greatly appreciated...

View 9 Replies


ADVERTISEMENT

Macro To Print Sheets With Value In Cell A1 But Print Dynamic Ranges On Certain Sheet

Sep 24, 2013

I've found some code which works to print certain pages with value in cell A1 but I need to print dynamic ranges on some of the sheets as they will have filters on so the rows ranges will be different each time.

So far this is what I have but the dynamic range part is not working:

VB:
Sub Print_All_Worksheets_With_Value_In_A1()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer

[Code] ....

View 3 Replies View Related

Macro To Print Selected Sheets ...

Mar 16, 2009

in creating a macro so that I can print from sheet 1 to sheet name "XYZ" as an array. In other words I want to print selected sheets as one command so that page number in the footer will automatically change.

View 14 Replies View Related

Print Event Macro To Run From 3 Sheets Only

Nov 9, 2011

with an event macro. Basically I have a workbook with 11 worksheets and have created a macro that increases the value of cell J3 by 1 in Sheet1. However at present it only works from Sheet5, but I now need it to run from Sheet6 and Sheet11 as well and not from any other worksheets. The code I have is as follows

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.CodeName = "Sheet5" Then
With Sheet1
.Range("J3").Value = .Range("J3").Value + 1
End With
End If
End Sub

View 1 Replies View Related

Using A Macro To Print All Sheets With A Twist

Feb 15, 2010

using a macro to print all sheets in a workbook. During this process each sheet has a print area to print as well. A button will be placed on all the sheets. No matter which sheet you are on I would like it to print all sheets. The code I have works I think, but I would like to not have to go by sheet names in case there are some added later, etc. Also, is there a way to do this without going to each sheet and the user seeing all this? Would like to do it in the "background" so to speak.

Sheets("sheet1").Select
'Range("B2").Select
Range("H1:K1").EntireColumn.Hidden = True
Range("O1:S1").EntireColumn.Hidden = True
Range("V1:W1").EntireColumn.Hidden = True
Range("Y1:AC1").EntireColumn.Hidden = True
Range("L1:N1").EntireColumn.Hidden = False
'Print range column A thru AD
ActiveSheet.PageSetup.PrintArea = "A1:AD84"
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.Zoom = 58
' .FitToPagesWide = 1
.BlackAndWhite = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, collate:=True

Range("H1:K1").EntireColumn.Hidden = False
Range("O1:S1").EntireColumn.Hidden = False
Range("V1:W1").EntireColumn.Hidden = False
Range("Y1:AC1").EntireColumn.Hidden = False
Range("L1:N1").EntireColumn.Hidden = True
Range("A1").Select

View 9 Replies View Related

Macro To Loop Through Sheets And Print Them Out Using An Array

Aug 4, 2009

I have created a macro that loops through and creates an array of the visible sheets. Now I would like for it to print each of those sheets out. I think my main mistake is in my declaration of the array type, since I have not worked with arrays much before.

how to make the following code operable. Currently when I run it I'm getting a "Run-time error (9): Subscript out of range" error.

View 2 Replies View Related

Macro: Print Ranges From Multiple Sheets

Dec 6, 2006

I am trying to write what I thought would be a simple macro to print out specific areas of my worksheets. I have shown the code below; the line causing the problem I have highlighted in RED. I am getting the following error message: "Select method of range class failed".

Reading other posts here. I think this may have something to do with the macro being assigned to a command button in one worksheet (AY114) and I am trying to get the macro to run on both the worksheet that the command button is in (AY114) as well as another worksheet (AY062).

Sub CommandButton1_Click()
Range("J2").Select
Sheets("AY114").Select
Range("A4:J53").Select
ActiveSheet.PageSetup.PrintArea = "$A$4:$J$53"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=45
Range("A56:M151").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$M$151"......................

View 4 Replies View Related

Macro To Print A Variable Number Of Multiple Sheets

Aug 25, 2010

I am trying to build a macro to work with a template file.

The template file has set sheet numbers and names (with one exception, see 4) below).

There are close to 40 sheets in all.
Some sheets are never printed.
Some sheets are always printed.
Many sheets are printed only if they are used.
Most of the sheets are 'break out' sheets and a variable number of them will be used.

So... Here is what I'm trying to accomplish.

1) Always print Sheet4(Overview1)
2) Always print Sheet6(Overview2)
3) Print Sheets 11 thru 40 IF value in cell G50 on these sheets is > 0. Note that this cell has a name (Total) and I would like to reference the name if possible. (It could happen that further evolution of the Workbook moves the cell up or down a row.)
4) Print Sheet38(Data Sort) IF it exists. Otherwise, print Sheet1(Data).
If the information on the Data sheet is limited (eg only 1 or 2 pages), we print it off directly. If the information is more extensive, we copy it to a new sheet and sort it (Data Sort).

I can achieve 1) and 2). I'm not quite sure how to go about 3) as I'm still not very good with macro loops. 4) I haven't tackled yet.

View 3 Replies View Related

Creating A Macro To Compare 2 Sheets In A Workbook And Print The Differences To A 3rd Sheet

Sep 30, 2009

Creating a macro to compare 2 sheets in a workbook and print the differences to a 3rd sheet.

Each sheet will have the same number of fields, 5 columns with the header in the first row.

All values in the cells are integer except for the last field which will be a character.

The key is the value in the 2nd column. If it's not in the other sheet, then it's a new record. If it's a new record then highlight it a color depending on what sheet contains the new record. Now if the key is the same in both sheets, then check the other columns to see what's different. If there is a difference, print the record for both sheets in the third sheet and highlight the differences. I attached a sample of what I want.

View 14 Replies View Related

Excel Will Not Print More Than First 5 Sheets When Tell It To Print All Sheets?

Dec 1, 2013

I have a Document with multiple sheets in it and I ask it to print entire document which would be 7 sheets (1 Page printed per sheet) but only the first five will print, then it comes up with a second print queue that I need to use and OK to print the balance of them with. I'm printing To a PDF creation program. it saves and Prince the first 5 pages then asks for another new name of the document to save the next few pages. my PDF Creator works fantastic with any other program. I just need to know how to tell it to print as many sheets as I have to print without it doing that. so I can print all sheets in a single PDF document.

View 2 Replies View Related

Stop Print Macro Firing Before Print Event

Oct 18, 2006

I have a "submit" button macro which user would click after he has finished his input. This macro would update a reference number on the worksheet named "orange" and then print out this worksheet.

My purpose is "orange" is printed out with a reference number.

Below is the 1st code.....

View 9 Replies View Related

Print 2 Sheets

Apr 25, 2009

I have 2 sheets that I want to print off, these are called Questions & Answers.
I have the following code to print off Answers, how do I amend this to print off Questions & Answers?

'Print!
.PrintOut
.Columns("A:A").Select
Selection.Font.ColorIndex = 1
.Range("A1").Select
.Visible = True
End With
Sheets("SEARCH").Select
End Sub

View 9 Replies View Related

Option To Print - Use Print Macro

Dec 30, 2008

I have a few macros that run in a sheet. After the macros are finnished I would like to present to the user a msgbox that asks if they would like to print. if "Yes" is selected, the print macro runs. If "No" is pressed then nothing happens.

View 3 Replies View Related

Disable Print But Allow Print From Macro

Jun 11, 2007

I have found a macro which disables all print features in excel. I have rwo other codes to print. When I put this macro, It also disables the print from the other macro. How can I use this code so that normal print features will be disabled but if someone takes the print from the macros, Print will be allowed. The code which disables the print is given below:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If PrtOK Then
Cancel = False
Else
MsgBox "Can't print from here!"
Cancel = True
End If
End Sub

View 9 Replies View Related

Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells

Feb 25, 2009

Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction

View 2 Replies View Related

How To Print Multiple Sheets

Jul 17, 2013

I have 23 sheets out of a much larger number of sheets, all in one workbook. How can I print only the first 23 sheets all at once?

View 6 Replies View Related

Vba To Print Multiple Sheets

Apr 5, 2009

I've been feverishly searching for a pre-existing macro to print specific sheets assigned manually by the macro to the windows default printer (or better still bring up the printer dialog box and you can select which printer!)

The macro is attached to an image on Sheet 3 called "Details"
The respective sheets I want to print in succession are Sheet 2 "Letter Of Estimation", Sheet 6 "Labour & Equipment SOR" and Sheet 7 "Labour Only SOR".

Can anyone help me with this please? I've been searching for a while and come up with various bits of code that perform intricate loops based on user input, and other unrelated tasks I can't seem to ween out of the code by myself.

View 8 Replies View Related

Print Active Sheets With VBA

Oct 22, 2011

I am trying to select a variable amount of worksheets in the workbook and print just those.

I was trying to do something like...

Code:
Sub print_sheets()
shtcount = Sheets("Master List").Range("A278")
For i = 2 To shtcount
Sheets(Array("i")).Select
Next i
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

...but this wasn't really working...

View 3 Replies View Related

Print Multiple Sheets At Once

Apr 19, 2008

I have a workbook consisting of about 20 worksheets. I have VBA code that prints any sheet that is used but skips any pages that are not used. This works fine but if there are more than one user printing sheets at the same time on our network printer, the pages get all mixed together and they have to pick through the stack to find their pages. Is there a way that the used pages can be assembled and then all printed as one printjob so that each users pages will all print together simplifying the sorting process.

View 9 Replies View Related

Print Array Of Sheets

May 20, 2008

I know I can print sheets as such:

Sheets(Array(2,4,5,8,9)).printout

I want to populate an array list based on sheet names, but cannot figure out the last line for syntax:

Dim ws As Worksheet, arr() As String
ReDim arr(0 To Sheets.Count-1)
For Each ws In Worksheets
If InStr(1, ws.Name, "Crp-") Or InStr(1, ws.Name, "Reg-") Or InStr(1, ws.Name, "Grp-") Then
arr(counter) = ws.Index
counter = counter + 1
End If
Next ws
Sheets(arr()).printout

As I debug it the array is filled with the proper sheets, but I for some reason cannot figure out the syntax for this line? Does it have anything to do with me declaring the array as a string and using integers as the index?

View 9 Replies View Related

Print All Sheets Without Opening WB

May 28, 2008

On right clicking a closed file and if print is clicked, it is opening the file and printing the activesheet only and then closing the file. I want that it should print all the sheets continously and not only the Active sheet. How ths can be done?

View 9 Replies View Related

Only Print Active Sheets

Aug 20, 2009

Is there any way to only print active sheets

i mean, i have 4 sheets in my workbook, and if i only use one sheet i dont want to print all 4 sheets

i would like to print sheet 1 if cell D19 has data in it
i would like to print sheet 2 if cell D61 has data in it
i would like to print sheet 3 if cell D103 has data in it
i would like to print sheet 4 if cell D145 has data in it

View 9 Replies View Related

Print Chosen Sheets

Oct 29, 2006

I have a workbook containing over 75 sheets. I would like to print only a select few of those sheets using a macro. I did download the "sheets to print" workbook submitted by Dave Hawley ,and have tried (in vain) to adapt the code so that it does not display ALL of the workhseets in the userform box. I want to limit the sheets displayed in this box to onnly those that I name.

View 8 Replies View Related

Print Visible Sheets Only

Dec 5, 2007

I use templates (.xlt) to generate reports for different organizations. In some cases certain pages in the reports are hidden.
When printing I get blank pages where pages are hidden. How do I not get these pages printed at all?
I do use page breaks to format the reoprts.

View 4 Replies View Related

Compare 2 Sheets - Print Variance

Jan 6, 2014

3 Sheet Excel document- What i'm trying to do is compare the contents of Column A sheet2, with Column J sheet3.

I would like only the variances printed on Sheet A. So- Sheet A says "The following was found in Sheet2!A, but not Sheet3!J"

Demo excel spreadsheet attached. Comparing "NASC Column A" with "RQ4 Column J"

View 4 Replies View Related

Print Only Visible Sheets To PDF Code?

Feb 27, 2012

I have a workbook with many worksheets. All I want to do is print to PDF those that are visible (I can use just the code name number can't I?) and ignore those that are hidden. It seems simple enough but it will run and do nothing.

Dim sMsg As String, FName As Variant
Dim myArray() As Integer
Dim i As Integer
Dim j As Integer

[Code]....

View 6 Replies View Related

How To Select Multiple Sheets And Print Them

Apr 14, 2014

I'm trying to select multiple sheets and print them out. At this time the code is only printing out the "Work Order" sheet. I'm guessing it's something to do with the PrintOut command trying to print the active sheet and not the array?

Code:

Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
ActiveSheet.PrintOut Copies:=1, Collate:=True

I have also tried the following but it just prints out every page in the workbook.

Code:

Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
Sheets.PrintOut Copies:=1, Collate:=True

I've also tried the PIDOOMA approach with this and failed

Code:

Dim TechnicianPack As Variant
TechnicianPack = Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
TechnicianPack.PrintOut Copies:=1, Collate:=True

View 7 Replies View Related

Set Different Print Areas For Multiple Sheets

Jun 23, 2014

In making my label printer, I will need to set the print area for multiple sheets based on a range generated in a cell. E.g. Sheet1 might need cells A1:P1200 set as print area, Sheet2 might need cells A1:G694 set as the print area, Sheet3 might not need to be printed, etc..

I have no problem generating a formula to state what the print area for each sheet should be, respectively, but passing that info into VBA has me really stumped. (Using indirect in the Page Setup menu doesn't work after the value has changed, it changes the value to a static reference, which is bizarre). I suspect I'd want to use the Sub Workbook_BeforePrint, but I'm not 100% on that. I will generally have 3 or more specific sheets selected using a button-triggered macro that gets a cell value of sheet names and selects those sheets.

View 4 Replies View Related

Print Sheets By Cell Reference

Aug 19, 2009

The following, within a macro prints the sheets requested:

Sheets(Array("Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

But I need to print only sheets the have data in cell A6. This is due to the footer page numbers (Page Of Page) I only need to print sheets with data in A6 of each sheet.

View 9 Replies View Related

Vba: Set Print Format Across Multiple Sheets

Jun 13, 2007

I'm trying to write a macro to select all the Sheets in a Workbook, and set some properties [Auto ColumnWidths, Landscape, and Fit to 1 page wide] for all of them.
I don't know the names of the sheets, nor how many there will be - this part is tagged on the end of a long macro that creates new files and pastes various data into them. The code below only seems to work on the Active sheet - not any of the others selected. Curiously, I can set a specific column width for all sheets, but not Auto Widths.

With ActiveWorkbook
Sheets.Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End With

Sheets.Select
ActiveWindow.Zoom = 80 ' This line works!
Cells.Select
Selection.RowHeight = 13.5
Selection.EntireColumn.AutoFit
Columns("C:C").Select
Selection.ColumnWidth = 34 ' This line works
Range("A1").Select

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved