Set Print Settings Per Worksheet

Mar 6, 2008

At my company we need to print our workbooks containing 3 to 24 worksheets. The first page should be printed on different paper than the other pages. From various forums I gathered that it's not possible to set the papertray using vba. Those forums suggest 3 things: 1. Windows Api functions (don't work at our company(security)), 2. SendKeys (don't work because of different printers, office versions and future-proofness) and 3.

Define printerdrivers per tray.

This last I did, so there are printerdrivers for tray3 and tray4.

With the following code I try to print the workbook:

Public Sub printSheets(strP1 As String, strP2 As String)
Dim curPrinter As String, firstPage As Worksheet, otherSheet As Worksheet, x As Integer
Set firstPage = Application. ActiveWorkbook.Sheets(1)
curPrinter = Application.ActivePrinter

Application.ActivePrinter = strP1
firstPage.PrintOut

The problem: The sheets are sent to the printers specified as strP1 and strP2, but the tray-settings from these printers are "overruled" by Excel; they are printed from the papertray that was the default of the printer when Excel was started.

Also, using the default printer dialog from Excel has the same problem; when the (windows-)default printer is Tray3 then all the pages come from there, even when printer Tray4 is selected as the printer. In Word or Acrobat etc the prints come from the right tray.

View 3 Replies


ADVERTISEMENT

Copying Print Settings From One Worksheet To Another

Aug 12, 2009

I am trying to copy the print settings(including the headers and footers) for one excel worksheet to another. I have found the following hint on internet and it works fine for copying the print setting(including the head/foot) but it is also grouping the worksheets. Click on the tab of the worksheet you want to copy. Then hold down the Shift or Ctrl key and click on the tab of the worksheet where you want the settings and click on File, Page Setup and OK. How can I just copy the print setting without grouping the worksheets?

View 4 Replies View Related

Changing Print Settings Within Single Worksheet

Mar 7, 2014

How do I change the print settings of individual pages within a worksheet. Specifically, I'd like my worksheet to print out 4 pages, the first as portrait and the remaining as landscape.

View 2 Replies View Related

Allow Users To Print Hidden Sheet But Not Change Print Settings

Jul 2, 2009

I am having trouble disabling the "Preview" button when calling Application.Dialogs(xldialogprint) in XL2003. I have an xlSheetVeryHidden worksheet containing a form which is made visible programatically when the user wishes to print a copy (code below). I want the user to be able to have access to the functionality of the xlDialogPrint dialog (i.e. select desired print destination, number of copies, pages per sheet etc.) but I also want to prevent them modifying the page setup of the protected worksheet by initiating a preview from the print dialog.

Private Sub btnPrintReturnForm_Click()

wbkRUSC.Unprotect sysPass 'unprotect workbook to enable changes to sheet visibility
' (sysPass is project constant string containing password)

shtStaffForm.Visible = xlSheetVisible 'make staff form visible

shtCalc.Visible = xlSheetVeryHidden 'hide main calculator in case user gets smart and hits..............................

View 5 Replies View Related

Save & Re-Use Print Settings

Feb 15, 2010

Is there a way to force a print RANGE, and add settings such as margins, and print to range to 1 page wide and 1 page tall? Can this be set with VBA? I tried to make a marcro but it crashes. This is for Excel 2002.

View 9 Replies View Related

Customize The Print Settings For The Template

May 8, 2009

I currently have a template that I have made and all the info and everything is fine on it, but the problem is with printing. Every time I go to print it wants to print out 100 copies of the same thing. I tried editing the original template by printing 1 copy then saving the template, but it still wants to print 100 copies with the next try. how to customize what the print settings are for the template and make them stay permanent with the template?

View 4 Replies View Related

Set Print Settings Multiple Workbooks

Jan 10, 2007

I have about 100 different workbooks that all need to have the same format and page set up for printing. The different workbooks just all need to be setup to have landscape.

View 4 Replies View Related

Setting Same Print Area And Settings Across All Worksheets?

Feb 2, 2014

Is it possible to set the same print area and print settings i.e. Landscape and 1 page tall by 2 pages wide, across all worksheets in an excel file? I have a file with 365 worksheets. They all have the exact same layout and need to be printed off daily. I can set the print settings every day manually but was wondering if there is an easy way to do them all in one go?

View 4 Replies View Related

Works With US English Settings, Errors If Italian Settings

May 19, 2008

The following line of VBA code executes properly if machine settings are US English, but throws a "Run-time error '1004': Application-defined or object-defined error" ? ...

View 9 Replies View Related

Excel 2007 :: Content Shown On Worksheet Different Than Print Preview And Print?

Jul 17, 2014

Since upgrading to Windows 2007 (I was already using Excel 2007) I am having issues with the content in the cells on the worksheet not appearing the same on Print Preview and when I print. On the worksheet the cell show to be at the best fit both horizontally and vertically. When I look at the contents under print preview, the contents are squashed from the top and cut off from the left. This happens whether I have the format in Top or Central align and is even worse if I use Bottom align. It is also somewhat worse if I have thickened boarders.

I am using TrueType Fonts.

View 8 Replies View Related

Print Previewed A Worksheet And Find A Tiny Image With A Small Portion Of The Print Area

Mar 27, 2009

I just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%. I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.

View 4 Replies View Related

Editing Existing Macro To Preserve Option Button Settings Upon Activating Worksheet With OBs

Sep 19, 2013

I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.

I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if

The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.

Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"

[Code] .......

View 1 Replies View Related

Excel 2007 :: How To Adjust Protected View Settings But Protected View Tab Is Missing From Trust Centre Settings

Dec 12, 2013

Excel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.

View 3 Replies View Related

Print From Within Worksheet

Jan 20, 2007

I have a workbook that I am sending out to others for them to fill out. I would like to have them be able to click on a cell (or text within a cell) and have Excel print that worksheet in landscape mode or at least open the print dialog box.

View 9 Replies View Related

Print All Worksheet

Jun 1, 2007

what is the vba to print every worksheet within an excel workbook?

also, i've been working on saving each worksheet and have the code for that, therefore, can show me how to loop through the count of worksheets (which will be changing every time i run the macro).

is it somelike like count the worksheet(s) first and then loop all of them. from there within the loop i can call the sub SAVE() and use the cmd "ActiveSheet.PrintOut".

i am trying to use "Worksheets.Move After:=Sheets(Sheets.Count)"
but i am not getting there.

View 9 Replies View Related

VBA To Print Worksheet Array?

Jul 14, 2014

Subscript 9 error that Ive been getting when I run this macro. It has worked in the past, but when I tried to test it today I keep getting errors on the array portion.

[Code] .....

View 3 Replies View Related

Print Excel Worksheet As PDF

Apr 4, 2012

I finally found a vba that will print my excel worksheet as a pdf.

Sub PrinttoPDF()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
End Sub

My questions are:

-How do I set a path for this printout. right now its printing out the pdf on my desktop.
-How can I name the pdf file to a value in a cell. right now i have to manually name.
-Is there a way to email this file from the path i just saved it in?

View 6 Replies View Related

Reset Worksheet After Print [{}]

Dec 28, 2006

I've set up a BeforePrint procedure as follows:


Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False

Dim ws As Excel.Worksheet
Set ws = ThisWorkbook.Worksheets("Objectives")
Dim i As Integer

With ws
For i = 9 To 33

If .Cells(i, 16) 1 Then GoTo NOTONE

If .Cells(i, 4).Value = "" Then
.Rows(i).EntireRow.Hidden = True
Else
.Cells(i, 4).Interior.ColorIndex = 2
End If

NOTONE:
Next i

End With

Application.ScreenUpdating = True


End Sub
... which works brilliantly....

My problem is that I don't know how to reset the sheet after the print!

Can anyone shed any light for me, please?

View 2 Replies View Related

Print Each Worksheet To Printer Of Same Name

Apr 14, 2009

I have a workbook with 150-some-odd sheets, each with a store number. I would like to loop through each sheet & print that sheet to the printer with the same name in my printers & faxes queue.

(eg. sheet 9604 would print to printer name oki9604)

View 9 Replies View Related

Print One Worksheet In Different Filtered Views As One PDF?

Jan 18, 2013

I have a worksheet set up with a number of pivot tables to show job summaries for each project manager. I created buttons labeled with each project manager that trigger a macro to filter the results to only show jobs for that specific project manager. There's one button for each project manager.

I'd like to create a macro that will essentially show each project manager's report and compile the result into one PDF. I only have 5 project managers so I don't need an array or anything if that makes it easier, I could write the code manually for each one.

So basically, it would do this:

1. Run macro to filter results to only show Project Manager #1 jobs.
2. "temporarily" print to PDF.
3. Run macro to filter results to only show Project Manager #2 jobs.
4. "temporarily" print to PDF.
5. Run Macro for Project Manager #3, etc.....
...............
Final step. Compile all the "temporary" pdfs into one file.

View 2 Replies View Related

Set Print Area Of Excel Worksheet

Apr 19, 2013

I would like to set the printer area of an Excel worksheet. I would like to print one page starting from row A3005-V3220. However, I cannot change the print area. Excel wants each row to be another page. When I drag the blue print area lines, nothing happens. So right now I have 216 pages and only want one!

View 3 Replies View Related

Print A Worksheet Based On Value In Another Cell

Jul 18, 2014

In Column C (C11:C28) I have Sheet Names - Descriptors and then in Column D (D11:D28) I have the "YES" or "NO" for whether the sheet should print There could be blanks rows or rows with just heading text on them.

This project uses Table of Contents and what is on the specific sheet I use a Formula [=CONCATENATE("Agenda1 - ",Agenda1!H1)] on some of the Rows. These all then Hyperlink to the appropriate worksheet. Not all Rows will have the Formula if it is easily identifiable.

The name of the sheet is Either the Name As Is Unless there is a hyphen and if there is a hyphen then the sheet name is the first word up to the space.

I'm sure this is doable with VBA I just don't know how to manipulate the - out of the names and do the appropriate sheet selections so that I can print all of the selected sheets.

My current data looks something like this:

Info Pages Print (Merged Cell Header Row)
Title YES
Implementation NO
<blank row>
Agendas Print (Another Merged Cell Header Row)
Agenda1 - FORMULA NAME1 YES
Agenda2 - FORMULA NAME2 NO
Agenda3 - FORMULA NAME3 NO
... thru Agenda7
Other Print (Merged Cell Header Row)
Other1 - Formula Name NO
Other2 - NO
Other3 - NO

My workbook has worksheets with names: Title, Implementation, Agenda1, Agenda2, Agenda3,...Other1, Other2, Other3

I want to select all of them that have the YES to Print and then send them to my printer.

View 3 Replies View Related

Macro To Print Selection On Worksheet?

Mar 18, 2013

I tried recording a macro but I am keep on getting an error. I need a macro which will print the selection of A1:D28 on a worksheet called Invoice. The print needs to be scaled at 165% of its normal size. i need it to be normal margins and if possible any printer.

View 1 Replies View Related

Macro To Print A Worksheet With A Button

May 24, 2007

I am trying to create a simple macro to print a worksheet with a button. The worksheet will have additions/deletions on a daily basis, so it need to adjust accordingly. I cannot figure out how to do the loop macro.

View 9 Replies View Related

Code Get Slow Down When Trying To Print The Worksheet

Aug 18, 2009

I have a some code that we use in our office that works ok on my pc. When others in the office try to use the macro the year gets changed to 19 instead of 09. The only problem I have is that the code seems to slow down when it is trying to print the worksheet out. Can the code be shortened up/cleaned up. We are looking for gridlines with inside /outside lines, landscape and left/right margins of .25

Sub IC_Delays()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Add
Windows("Delays List.xls").Activate
Range("F15:N" & Cells(Rows.Count, "F").End(xlUp).Row).Copy
Windows("Book4").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range("A1:I" & Cells(Rows.Count, "A").End(xlUp).Row).Select.........

View 9 Replies View Related

Macro To Print The Invoice Worksheet

Jul 15, 2006

I made a macro to print the Invoice worksheet and I added a button to run the macro but I don't think I did it correctly.

Sub Print_Invoice()
Range("A1:G35").Select
Selection.PrintOut Copies:=1, Collate:=True
End Sub

This is the information I used for the command button:

Private Sub CommandButton1_Click()
Range("A2:G34").Select
Selection.PrintOut Copies:=1, Collate:=True
End Sub

View 4 Replies View Related

Print Specified Range On Worksheet From Userform

Jul 26, 2006

I have a worksheet called "ComplaintData".

In this worksheet I have data in following range : "N1:V2".

N1 to V1 is headings and N2 to V2 is data transfered from listbox on userform.

I am trying to print this range via command button on my userform.

I am wondering anyone can help me with the code for selecting and printing this range when user clicks on the "Print" command button on my userform?

how I can format this print out to look different than the tabular format? I mean if possible I would like to print this in portrait, fit to one page and possibly not look like a table. (If it has to look like a table then I will have to go with landscape as the table wont fit portrait without getting too small and un-readable)

View 6 Replies View Related

How Can I Print A Worksheet Excluding Blank Rows

Aug 22, 2006

I want to print a worksheet where their are blank rows for future use between the main body of the data and the total row. I want to exclude the blank rows.

View 11 Replies View Related

Macros To Print Worksheet To PDF And Email To Recipients?

Mar 4, 2014

I have been trying to write a couple of macros;

1) save file as pdf, to be saved as filename=(cell in A1 & todays date (format yyyy-mm-dd)) and overwrite any existing file of the same name.

2) Email the pdf to a set of recipients round in Range "A2:A3", and subject = (cell in A1 & todays date (format yyyy-mm-dd))

View 5 Replies View Related

Clear And Print Tabs In A Worksheet With Checkboxes

Feb 4, 2009

I have a workbook with 100 tabs and I have an index page with checkboxes for each tab. I need a macro that will clear all of the checkboxes on the page with one button and a separate macro that will print only the checked tabs.

Can anyone write that code for me so I can just add it into my index tab?

View 6 Replies View Related







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