Macro To Set Print Area Based On Cell Value(s)
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
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
Apr 3, 2012
I have a code Ive been trying to get to work. It works fine with selecting the print area based off a cell value, it just wont update when ever the value changes.
This is on the Sheet's code
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target Range("A1") Then Exit Sub
Call PrintbyName
End Sub
And this is in a module
Sub PrintbyName()
Dim rng1 As Range, rng2 As Range, rng3 As Range, rngPrint As Range
'Refer to the named ranges
With Worksheets("Permit Page")
Set rng1 = Range("$A$1:$bx$603")
Set rng2 = Range("$A$1:$bx$493")
Set rng3 = Range("$A$1:$bx$493,$a$549:$bx$603")
[code]......
I wasnt sure if I was suppose to fill out where it says "nothing".
View 9 Replies
View Related
Mar 12, 2014
I am trying to set the print area based on cell values, but I keep getting errors when running the macro. It fails where I have highlighted, but rc.Value shows as the proper number.
[Code] ....
View 4 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
May 15, 2008
I have a worksheet which contains data for 6 different groups. I have a sort macro already that sorts them into groups (group 1, group 2, etc.) What I want to do know is set the Print Area so I can just print each group individually with a button("print group 1", etc.)
The worksheet is set up as follows:
Columns B-H are the data I want to have printed. The number of rows is different for each group. I want to set the Print Area based on Column H. So, if H=1, set the Print Area for Columns B-H and all rows that have H=1. This seems like it should be simple enough, but I haven't quite been able to put the pieces together yet.
View 10 Replies
View Related
Jan 16, 2007
I have a worksheet named "TQUOTE" with the print area defined as A1:E:286 resulting in 5 pages printing ,however if there are only 3 pages of data I will be left with 2 blank pages being printed. (along with the header rows which are set to repeat for each page ). I should mention this worksheet is included in a list of sheets to print based on the response to a user form. Would it be possible to have the print area change based on the an empty cell that related to the next page break? eg. If the page break for page 1 ends at row 55, if a cell in row 56 were empty then set the print area to be A1:E55 If the page break for page 2 ends at row 85, if a cell in row 86 were empty then set the print area to be A1:E85 and so on ?? I do not have the skills yet to write this macro , and i bow to thiose
View 6 Replies
View Related
Feb 26, 2012
I am trying to dynamically set a print area based upon a formula from the contents of 2 cells. What I need is the print area range to be C1 to Ix, where x is the result of ((A2/B2)+2).
View 2 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
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
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
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
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
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
View Related
Mar 8, 2007
I have a template worksheet that several people use and print from.
The range on this worksheet used is A1:C499
I want the VBA to look at tha last cell that is populated in column A and set the print area to suit.
i.e. the last cell used in column A could be A277. then I'd want the print area to be A1:C277.
View 9 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
Feb 6, 2014
I have a calculator and I am trying to set a macro that will take the cells highlighted copy them to another sheet, change the format to standard( no background color) make the data fit in one page and print it.
this is what I got so far:
Sub Set_Print()
'
' Set_Print Macro
' set printing area and print
[Code]......
View 3 Replies
View Related
Jul 3, 2014
I have data (part numbers) in column B. They are alphanumeric, eg 29 EE.
When I receive parts I enter the quantity of pallets received in column C next to the part number in column B.
Sometimes certain parts are not received therefore the relevant cell in column C would be left blank. There are a total of 30 part numbers in column B.
I also have an A4 landscape sheet (when printed) which is formatted into two rectangular blocks (merged cells) with fonts sized 200. The lower section contains the NOW() function and the upper section contains a part number which is entered manually. If I receive 10 pallets of 29 EE I will then print out 10 copies of the sheet with the part number and current date.
The same applies with the next part number 29 HE, if I receive 3 pallets of this part I then edit the part number for the A4 sheet and then print 3 copies.
I would like to be able to just enter the quantities received into column C and then select a macro button to print out all the sheets automatically for each part.
The reason for this is to enable older stock to be used first which can be easily identified with an A4 sheet attached when it is put away in the warehouse racking.
View 4 Replies
View Related
Dec 15, 2008
got a great bit of code the other day from this forum to automatically print 'x' amount of copies based on the value in cell (see below)
View 2 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
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
