Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value
Dec 20, 2007
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints !
Is this possible?
FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells.
I used this code from a post on a similar topic, but excel is complaining code
in bold
Private Sub Print_Area_Click()
Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
End Sub
View 9 Replies
ADVERTISEMENT
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
Feb 26, 2009
I am trying to write a macro that publishes the print area as a non-interactive web-page to a file that sits in the same location as the spreadsheet from which the macro is being run.
I am using the the following macro:
View 14 Replies
View Related
Feb 12, 2013
I 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.
View 1 Replies
View Related
Apr 20, 2007
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
View 9 Replies
View Related
Jul 20, 2007
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.
View 9 Replies
View Related
Oct 11, 2007
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:
Range("A1:M1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$M$76"
The next time i run the macro the selected cells won't be A1:M76 - how do i have the macro set the print area to whatever is selected?
View 9 Replies
View Related
Apr 10, 2009
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
View 9 Replies
View Related
Mar 18, 2009
I have a document which will require signatures after printed and want to keep the signature portion at the bottom of the page (currently rows 102-104). The problem is that the document will not need to be that long for every person and was wondering if it would be possible to have a macro that checks (C7:C:100), stops printing when it reaches the blank cells, and then prints those last 2 rows?
View 8 Replies
View Related
Jun 13, 2009
The macro below works perfectly except for one problem. I only want it
to set the print area from Column A - Column M . It currently selects data i have in Columns N and beyond and i do not need that to print. From A - M is 13 columns.
View 4 Replies
View Related
Jun 8, 2007
In the range A1:H89 I have expanding and retracting values - all of which is determined by the numbers in column A. Column A is simply A1+1 copied down. The value of A1 is determined by the value of another cell, based on age. If the value in column A exceeds a specific figure (let's say 50), formulas in columns B - H result in answers of "" (this is "nothing"?). Assume that the value of 50 is reached in A20. All cells from A21 (inclusive) down, including B21 - H21, will have the values "" in them and show up as nothing on my screen. When I print, I'd like to only print those cells where data is, i.e. cells A1:H20.
Sub Drukwerk()
With ActiveSheet.PageSetup
.PrintTitleRows = "$7:$9"
.PrintTitleColumns = ""
End With
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.PageSetup.PrintArea = "A1:H" & LastRow
' ActiveSheet.PageSetup.PrintArea = "$A$1:$H$89"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "".......................
View 4 Replies
View Related
Mar 15, 2009
I need a macro to select the area within the blue invoice sheet box, so everything within the blue invoice sheet box is selected , and then the selection should be printed. I will have hundreds of these invoice slips made, right below each other, i was wondering once this is done, is there an easier way to to have each invoice selected, instead of making a custom button for each sheet ( which selects just that invoice , and prints that selection ).
( Column m through v, starting at row 2 stoping at row 68, is the selection required in this example, everythign within the blue box. ).
View 13 Replies
View Related
Dec 8, 2011
I'm looking for a way to set the print area based on the value of a cell. I have a worksheet that prints a 12-page document, with data pulled from separate sheets. At the bottom of these twelve pages is an additional 2-page section that I only need in certain instances.
For example, if a cell (let's call it A1) on Sheet1 says "brown," I need the print area set to include these additional pages. If that same cell ('Sheet1'!A1) reads "yellow," however, I don't need to include the 2 pages in the print area.
View 1 Replies
View Related
Apr 2, 2014
My macro "prints" (saves) my sheet as Pdf-file.
I wish to improve this such as print/save is based on the active area I choose at the time.
Now I must hide a lot of columns (show only the ones I will print) before "printing":
Columns("A:Z").Select
ActiveSheet.PageSetup.PrintArea = "$A:$Z"
I just want to select some columns and set this range as a print.setup area.
View 2 Replies
View Related
May 29, 2014
I have a report that has a set template except that the number of columns change.
I have 3 rectangular shapes with text boxes inside each rectangle. The Shapes take up space vertically between row 8 and 15. In some reports the columns will change, the size of columns might change due to longer text. Is there a macro that will select all the shapes and text boxes and center them column wise between the print area?
View 1 Replies
View Related
Jun 25, 2008
I am looking to create a macro that will ask the user to define the row number of the beginning and ending locations that they would like to print. The columns are fixed in that the first starting row will be from column A and the ending row will be column M.
I have built a pipe tally used in the oilfield that keeps track of large amounts of pipe figures but very often is the case that there is the need to print a section of the tally data in reference to a certain depth. With the vast knowledge base located here I was able to build the first macro that prints the whole active tally but now I need something more refined.
Below is one of the copies of the macro that I was trying to get to work and it is missing a couple of things but the most important part shows up and that is how to get the inputbox variables into the defined print area.
Sub TallyVariable()
Dim StartRow As String
Dim EndRow As String
StartRow = InputBox("Please Enter Starting Row you would like to print")
EndRow = InputBox("Please Enter Last Row you would like to print")
ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address
End Sub
View 9 Replies
View Related
Jun 28, 2008
We run a small gardening shop and use a simple spreadsheet to track of various things.
Row A contains headers, with the data following in the rows underneath. This table is now quite large, and we therefore hide 600 or so rows so that only rows with data from the last week is shown. We often need to print this for easy reference. I currently do this by highlighting the area I want and setting it as print area. The print therefore doesn't include hidden cells, which is what I need.
The problem we have, therefore, is that we have to manually select this print area each time. My experience with macros is VERY limited, to the point where I can record one which will select the print area and print. However, as the list gets longer and more rows are hidden the range obviously needs to change, and my simple macro will not keep up.
So my question is as follows. Is there a simple macro I could write to assign to a button that could "keep up" with the moving range? So either it always prints the header row and the, for example, 50 rows beneath (exlcuding the hidden ones), or, even better, it prints the header row and all rows with data in them that are relevant to the last week. Each row has a cell for the date it concerns so assume this may be possible?
View 9 Replies
View Related
May 29, 2009
I 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.
View 2 Replies
View Related
Dec 4, 2012
I would like to send a Excel printing area to a mail recipient by using a macro that:
2) Save the printing area as a pdf - file - use a temporary filename
1) Opens MS Outlook mail
3) Attach the temp-file to the mail
View 6 Replies
View Related
Apr 11, 2013
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 Related
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
Mar 18, 2007
I have a workbook that need a macro to print current active cell
and the surrounding 6 rows 4 cols.
Also the selection printed need to fit
to page pref landscape on printing
the active cell varies day to
View 9 Replies
View Related
Aug 24, 2007
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.
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.
View 9 Replies
View Related
Jul 24, 2007
I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores.
I would like it to be able to return to the last cell that I was punching a score in...I used "ActiveCell.SpecialCells(xlLastCell).Select". I have also used " x= cells(Rows.count,2).end(xlUP).row" followed by "cells(x+1,2).select", but both with no luck...
Sub Hide_Scores()
Range("B15:EU35").Select
ActiveSheet. Unprotect
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
View 6 Replies
View Related
Oct 17, 2008
when he copies some data across a worksheet - the new data has a bold format, so he has to unbold everytime.
I can't see any obvious reason why this is happening.
View 9 Replies
View Related
Dec 29, 2008
I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area.
Sub Insert_for_SQs()
Do
Selection.EntireRow.Insert
ActiveCell.Offset(6, 0).EntireRow.Activate
Selection.EntireRow.Insert
ActiveCell.Offset(12, 0).EntireRow.Activate
Loop
End Sub
View 9 Replies
View Related
Jan 16, 2012
First I will post the data:
Weekly Roster
F98AKHTAR99DEEPAK100NAPA'A101OUKASH102ROBINSON103RUSSELL104SALUNI105S
PARTIATIS106SPICE107TAN108TORRES. Jr109VAN STEEN110ACCIARITO111112113114115116117118119120
In this data, as you can see there are a lot of cells/rows that are empty. I have a macro that will copy the cells F98:F120 to F99:F121 and then copy F121 to F98, and finally delete the value from F121. In this case, it will give me a blank cell at the top and the data hasn't rotated really. All the names will be in same position.
But I DON'T want that. I want the macro to look for the last cell/row with a valid value in it and rotate the cells so that I end up having ACCIARITO at the top and everybody else to move down one row.
View 6 Replies
View Related
May 29, 2013
I've got an old Excel sheet with Stephen Bullen's function for returning the active filter criteria (Rob on Programming: Excel: Displaying Autofilter Criteria). My status sheet may be filtered in multiple ways, and when the user is happy with the filter selections, she can create a powerpoint file with a graph and a summary of the filtered table. As we are using Excel 2010, users are very likely to select more than two filters.
Example: Range A1:E100 has the following headers: Field, Installation, Project,Type, Phase.
The controller wants to filter on:
Field equals north or south or west Phase equals completed
The manager for Field South wants to filter on:
Field equals southType equals maintenance or repair or modification Installation begins with Zeus.
As the filters are not shown when I copy the table to powerpoint, I would like to create a summary of the user's active filters that is pasted into a sheet (for subsequent copying to powerpoint). For the users in the example above, that table would look something like this:
Controller:
Active filters
Field: north, south, west
Phase: completed
Manager, Field South:
Active filters
Field: south
Type: maintenance, repair, modification
Installation: Zeus*
I've looked at various functions intended to take Stephen Bullen's code into Excel 2010's multiple criteria world (e.g. this: User Defined Function to Display AutoFilter Criteria for More Than Two Criteria in Excel 2007 / Excel 2010), but I have not been able to convert it to a functioning macro.
Any code that could be used for this sort of task, or any tips for relevant code?
View 2 Replies
View Related
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
Nov 9, 2006
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.
View 14 Replies
View Related
