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 RepliesI have looked for a way to set the print area of a worksheet through vb.
I would like it to find the last row of the worksheet and set it to be printed.
Some of the examples take it beyond the last row of data, because the the code that may be in the empty cells.
HTML Code: [URL] .....
I'm looking for code that would set print area when printing the output of my Macro. Print area will always be the same for all two pages. I need to display first two tables on page one, and the next two on page two. All four tables will always contain the same number of columns and rows. I'm trying to automate this process as much as possible.
View 1 Replies View RelatedI need to write a macro to set the print area to the first two columns (A & B), and the last 12 columns (the last column may change). In both cases I need to print all rows (start is row 1, last row is variable). I am trying to achieve something like the "Freeze Pane" effect with the printer. The first two rows contain column headers (dates). The first two columns contain information that needs to be included on the printout, whilst the last 12 columns contain the most recent data.
Various cells withing the selected ranges may be blank, but no row or column will be entirely blank.
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 RelatedI have a workbook with multiple sheets that I need to print.
Is there a way to create a fixed print area within which you can mess around with formatting without extending or shrinking the print area?
I have tried adjusting the margins settings so that they are all the same, but this does nothing to keep a fixed print area.
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..............................
How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time
View 7 Replies View RelatedI 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 RelatedIs 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 RelatedAt 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.
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 RelatedI 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 RelatedI 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 RelatedHow 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 Relatedwith creating vb code that will select certain rows in a spreadsheet, set the print area to those rows and print them out, and then reset the print area back to default.
The data is all on one sheet, but is split into 5 different areas, each below the next, with the column headers at the top of each.
e.g.
header_____header_____header
data_______data_______data
data_______data_______data
data_______data_______data
header_____header_____header
data_______data_______data
data_______data_______data
data_______data_______data
and so on. At the press of a button it'll set the print area to the first section and print (the section includes the headers as well as the data). Then at the press of another button it'll set the print area to the next section. There could be any amount of rows of data in each section so it needs to cope with that.
I have a battery sizing workbook, which has various tables and active cell VBA routines, that enable the user to input a number and then select a cell from the table presented on the front worksheet. This will then display the solution including other data. I have now been requested to share the workbook and all I seem to do is lock it up and therefore stop and calculations. Therefore your assistance is required in two area's:-
1. How to protect the workbook and allow the user to select a cell on the front worksheet, without all the all the VBA routines appearing to grind to a halt! I can lock an hide all the subsequent worksheets without freezing the front worksheet.
2. I can hide all the toolbars, row and column headings under my Tools/Options which will display the worksheet neatly...... Therefore can I some how instruct the users Excel to turn these off when loading?
I have a sheet that will be about 5 pages long if printed. To minimize wasted ink and paper, using VB I would like to be able to have it only print the portion uses (data entered) along with a section at the top and bottom. The section at the top would print on the top of the first page and the section at the bottom would print on the last page.
I know I could record a macro to select an area and print that but it would not do what I am trying to accomplish.
I have an excel sheet with lots of rows (it goes up to BAA) I would like to be able to print certain parts with a VBA
for instance if Sales is selected from a drop down (lets say A1) the macro will print
Area : C7:Z500
Scaling: fit to 1 page
Orientation: landscape
Color: Color
or if Sales next year is selected
Area : C7:D7 AA7:AZ500
Scaling: fit to 1 page
Orientation: landscape
Color: Color
etc....
I use the code below to print a portion of a sheet. My problem is that the area always changes. The $A$476 should be the first cell in that column that contains "HEA" The "536" in $F$536 is the last row that contains data. There are conditional formats all the way down to row 1,000 but not data
Private Sub CommandButton1_Click()
Dim strCurrentPrinter As String, strNetworkPrinter As String
****strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF")
****If Len(strNetworkPrinter) > 0 Then ' found the network printer
********strCurrentPrinter = Application.ActivePrinter
********' change to the network printer
********Application.ActivePrinter = strNetworkPrinter
********ActiveSheet.PageSetup.PrintArea = "$A$476:$F$536"
********ActiveSheet.PrintOut** 'print something
********' change back to the previously active printer
********Application.ActivePrinter = strCurrentPrinter
********ActiveSheet.PageSetup.PrintArea = ""
****End If
End Sub
Obviously there is a function also that finds the full network printer name, I didn't think it was important, because my issue is the print area. Everything else works fine. If you need it let me know and I will post it.
Looking for a macro that will set the print area on an accounts spreadsheet to the last non blank row, bearing in mind that all the rows contain formulas, have tried some macros but they assume the cells with formulas in them are non blank
View 4 Replies View RelatedI have a excel sheet with multiple sheets in it.And I have a VBA macro which split all those sheets into one file each.When I am trying to print one file(SHeet) which has more than 15 columns it is printing in two pages(Width).Is there any code i can use so that when ever i open a file which was split by me and give print command the width should fit to the page and peint.
View 6 Replies View RelatedGoal is to select range from active cell ( where coursos is ) to the top of that row. For some reason
ActiveSheet.PageSetup.PrintArea = Range("xey1":ActiveCell())
Does not work?
Making a macro that selects active cells (so anything with text in) and sets that as the print area.
Using the default builder I have:
Range("A1:AA44").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=30
Range(Selection, Selection.End(xlUp)).Select
Range("A1:AA692").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$AH$692"
[Code]...
Can trim this code with something
Range("any containing data").Select
ActiveSheet.PageSetup.PrintArea = "selection"
copying a document that was made in word into Excel. I set the print area and page size but it seems I can keep adding columns. When I check the print preview it still shows the one page but it appears wider.Will it automatically create a new page if the page size limit is reached?
View 2 Replies View RelatedExcel 2003 used to have a feature where the user can select and set the print area. I do not see this feature in Excel 2007. So, how dow I set the print area in Excel 2007?
View 2 Replies View RelatedI can define a name to calculate the desired Print Area for a page. For example, print_area_notes: =Notes!$A$1:INDEX(Notes!$A:$Z,final_Note,5). I can then use that name when defining the Print Area via Page Setup.
The problem is it calculates the cell range immediately and substitutes that in the Print Area field. When print_area_notes changes the actual Print Area does not. Is there some formula I can enter in Print Area that will allow this value to be set dynamically? I have tried setting Print Area to =indirect("print_area_notes") but it doesn't like that. I could, but prefer not to, write a VBA function to set the Print Area but even then how do I set it to be called automatically? I'd prefer to be able to enter a formula and do it without VBA code because you then have the security issues every time the spreadsheet is opened.
I have 2 codes that set up different "print areas". Every time i run one of the codes the print area doesn't get set unless i run the same code again. So if i run code A which will set the print area with no issues and then right afterwards i run code B then code B's print area won't get set unless i run code B a 2nd time. And if i now i run code A, the same issue happens and the print area to Code A won't get set properly unless i run the code again.
Here is part of the code that discusses the print area:
[Code] .....
I'm trying to dynamically set the print area for a given worksheet. I use the code below and it works perfectly when it's executed upon opening the tab; however, the worksheet has filters which may lengthen/shorten the required print area.
Is it possible to execute the code below when the user selects print rather than when opening the tab?
[Code] ......
I'm using Excel 2003.
I have a spreadsheet used for costing jobs giving a detailed breakdown of parts and time. Sometimes the customer requests a copy so I'm creating a macro for my boss that prints the spreadsheet, less the hidden columns that I don't want the customer to see. This I can manage.
My question is this: Is there a way within the macro to set the print area to change to where the last cell is automatically?
Each spreadsheet is a different length dependent upon the work required and setting a larger print area to compensate would use excess paper and ink.