Conditional Formatting Lost Outside Print Area
Nov 7, 2012
I have a three sheet workbook that I created
The first sheet is virtually blank it allows the user to cut and paste dispatch info, and customer info from Lotus Notes Dispatching which is then referenced to auto populate appropriate cells in Service Report(third sheet)
The second sheet uses significant conditional formatting to aid the user in entering data in a very easy to use straight down vertical manner.
There are 74 rows of data entered followed by several comment areas which all also auto populate to the third sheet(service report).
Issues I am having, I only want the third sheet (service report) to print, but I want all conditional formatting to be visible to the user.
At one point I put all three portions on a single sheet and set print range to exclude that which I don't want printed but then all formatting is masked and cells are just gray.
I would prefer to have the three sheets remain separate and to only print the third sheet, but need conditional formatting to remain functional and visible.
View 6 Replies
ADVERTISEMENT
Jul 30, 2007
I am running office 2007. I am using conditional formatting in a pivot table to highlight rows where the value in the last column meets a particular criterion. However everytime I update the table the formatting gets lost. I have looked through all the threads I can find to establish if there is some VBA code that I can use to reapply the required conditional formatting after each update
View 2 Replies
View Related
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 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 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
Nov 9, 2008
I have a workbook that is used by others and has been working reasonably well for several years. The users are youngsters who tend to play with it when a bit bored but I usually manage to put things right. They seem to have done something special this time.
The formatting for dates, time and money has gone and I cannot correct it. I have tried the usual formatting of cells, painting formats from other sheets, copying and pasting formats all to no avail. The whole sheet is effected so that new entries appear the same way although the correct format appears in the formula bar.
I don't know if this is relevant but the sheet relies on VBA form inputs. The code is password protected and only I know the password.
The PC is not connect to the internet or a network so I have ruled out a virus. (As far as I know, no floppies or USB sticks have been used on it either.)
View 14 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
Feb 3, 2013
Running Windows 7, Office/Excel 2007
I have a macro which performs some conditional formatting on a worksheet. Based on the value in cells in one column, if the value meets the criteria some simple formatting is performed (row is colored, font changes for that row etc).
The macro then calls a second macro which performs the same evaluation on the same cells from the same column. If the value meets the criteria (same as previously) it is supposed to change the value of the cell by 1/2.
Both macros work, however the 2nd macro also seems to remove all of the formatting, font changes, coloring etc that occurred from the first macro. I have had the first macro save the active workbook and close and have the 2nd macro re-open the file but it sill overwrites the formatting. If I do not have the 2nd macro run the format changes are saved. I have tried saving the file after the first macro runs with .xls and .xlsx extension and no luck.
View 3 Replies
View Related
Jul 16, 2014
I have a text file that I need to open in MS Excel 2007. The file contains the following data. (Each column is delimited by the "|" character.)
Part #|Inventory ID
1743|213,221
1864|10,40
1948|1170,1180
5265|100,104,107,10004
Ultimately, I need three columns but the data needs to look the same as it did before I opened the file.
When I open the file for the first time, I use “Text to Columns” to delimit the fields by the "|" character. The problem is that the Inventory IDs in the middle column get a bit whacky.
Here's what I'm left with:
Part # Inventory ID
1743 213,221
1864 10,40
1948 11,701,180
5265 10,010,410,710,004
The comma is retained appropriately when there are two 2- or 3-digit Inventory IDs.
When a row has multiple, longer Inventory IDs or Inventory IDs that vary in length, the comma is moved to every 3rd decimal place.
I tried putting quotation marks around the data before I opened the text file for the first time but that didn’t do anything. I tried Custom Formatting to retain the comma position (0000,0000) but this only worked when I had two 4 digit IDs. I also changed the number type to Text and General. Once the comma was changed, I couldn’t figure out how to get it back.
How to best retain the format of my Inventory IDs.
View 6 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
Jul 18, 2012
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....
View 9 Replies
View Related
Oct 2, 2007
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.
View 5 Replies
View Related
Feb 18, 2007
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 Related
Oct 7, 2012
Goal 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?
View 3 Replies
View Related
Feb 6, 2014
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"
View 1 Replies
View Related
Mar 29, 2014
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 Related
Jan 22, 2009
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?
View 2 Replies
View Related
Feb 5, 2009
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.
View 3 Replies
View Related
Jul 6, 2014
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] .....
View 6 Replies
View Related
Aug 19, 2014
I 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] .....
View 2 Replies
View Related
Feb 12, 2014
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] ......
View 4 Replies
View Related
Nov 4, 2009
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.
View 10 Replies
View Related
Jul 19, 2014
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 Related
Jan 29, 2007
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.
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
May 7, 2008
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.
View 9 Replies
View Related
Oct 24, 2008
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
View 9 Replies
View Related
Jan 19, 2009
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
View 9 Replies
View Related
