I have a hard time transitioning from other examples to my workbook.
What I am after, is for the print area on 2 sheets to be set by the count of non-empty cells in column B on the first sheet.
I have a maximum number of entries of 200 (rows) plus a header row. So, my entry #200 is in worksheet row 201 (cell B201)
In cell B203 I use formula "COUNTA" to tell me how many cells are not empty. As an example, this number might be 36.
The print area should be limited to worksheet row 37 (1 more than 36 due to the header row). My columns are always the same, A thru I.
On a second worksheet, measurements are recorded, 10 per row. So, if I have 36 items to be measured, I use 4 rows (10 per row=3 full rows and 6/10 of the 4th row).
I would like to have the print areas set automatically, based on this number in cell B203 (see above notes). On the 1st sheet (using example of 36 entries), my print area needs to be thru row 37, columns A thru I. On the 2nd sheet, my print area needs to increase 10 rows every time B203 increases by 10.
(this 2nd sheet has other info on it, so when the print area increases, it needs to go in steps of 10 rows. as an example, if B203=8, then the print area on the 2nd sheet includes cell A1 thru cell K13, but if B203=12, then the print area would be cell A1 thru cell K23)
if there is a way with functions or code to automatically set the print area. I would like to have it set to start at A1 and print to column G & the first ROW with a zero value in column F.
The ROW in which the first zero value appears will be the only variable. It will always be column G.
Is there a way to set Excel to only print rows that have data in them? I have an end user spreadsheet where the number of rows they have each time differs and I don't want them to have to manually set the print area each time.
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
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
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.
with 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.
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 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 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
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?
Excel 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?
I 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:
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?
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.
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.
Is there a macro that can automatically set the print area by determining what the used range is. For example, If P96 is my last used cell, I would want a1: P96 as my print area.
My macro selects cells containing data - then i want the macro to set the print area to those selected cells - works fine - but the selected cells won't be the same each time i run the macro. Here's how it looks now:
I have some code to select a print area depending on wether or not there is data in a cell. The code I have so far partially works but not quite like I need it to. Here's what I have,
Sub selectprintarea() Select Case True
Case Range("C5").Value > 0 Range("A1:M47").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$M$47"
Case Range("Q5").Value > 0 Range("A1:AA47").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$47"
End Select End Sub
It will select the print range if there is data in C5 but if there is data in C5 and Q5 it only selects the first print area. I know I need an "If - Else" or something of that nature but I cant figure that out. Also I need it to look at other worksheets not just the active one. I've tried a replacing the ActiveSheet with Worksheets (Sheet1). but that gives me an error.
I am having trouble setting print area on rows that can vary from 100 to 6000 rows. This code works good except that the print area cuts off the last row of data every time. "AreaBegin"=row 6 and "AreaEnd" = to last row after a set number of rows have been inserted based on a counter.
Basically how do I get this code to capture the last row for print area?
Sub SetArea() ActiveSheet.PageSetup.PrintArea = Range("AreaBegin", Range("AreaEnd").End(xlUp)).Address
I wish the print area to be set based on the number entered into cell "D58". My code so far is:
Sub Worksheet_SelectionChange(ByVal Target As Range) Dim NumPages As Range Set NumPages = Range("D48") Worksheets("Sheet6").PageSetup.PrintArea = Worksheets("Sheet6").Range(0, 0).Resize(NumPages * 21, 47) End Sub
I am looking to set the print area with a macro, below is what I had hoped would work. It does not. I get unable to get Vlookup property of worksheetFunction class error.
Sub Printzed() Start = Range("C6").Address Finish = Res = Application.WorksheetFunction.VLookup(I5, Range("prlu"), 2).Address
myPrintArea = Start & ":" & Finish ActiveSheet.PageSetup.PrintArea = myPrintArea End Sub
I am trying to write a macro that will allow the user to print out the sheet from the beginning to the last used row. The catch is that I have formats and formulas all the way to the end (A1:O319). My basic format is a frozen Pane (for a header and summary) from A1:O18, then the user inputs their data starting on B19. The columns never change, and the rows will be variable. I tried a dynamic range to no avail, and have most recently tried this
Sub printter() Dim myrange Dim Rng, cell As Range Set myrange = Range("A320").End(xlUp) Set Rng = Range("A1:" & myrange.Address) For Each cell In Rng If cell = "" Then myrange = cell.Address MsgBox cell.Address ActiveSheet.PageSetup.PrintArea = "$A$1:O" & cell.Row ActiveSheet.PrintOut Exit Sub End If Next End Sub
My next plan would be to write to check to see the last used cell in column B, since that cell has only formating, no formula, and is a required entry cell
