How to get Excel to disregard formulas when Auto selecting print pages...
Posted by Bill on August 24, 2001 7:30 AM
I have a workbook with 2 spreadsheets. I want the 1st column of sheet 2 to reference the cells of the 1st column of sheet one, so that sheet 2 automatically fills in the data entered from sheet 1. The problem is, I don't know how many rows the end user will need, so I have copied the reference formulas (links to the other worksheet) into almost 5000 rows down. The problem is, Excel regards the "hidden" formulas as something that needs to be printed. The result is several hundred blank pages being added when the 2nd worksheet is printed. Is there a way to get excel to ignore the contents of a certain set of cells when automatically choosing what to print? I can't use a set print area, because the print area varies every time the sheet is used. Any help would be appreciated.
Posted by cpod on August 24, 2001 8:33 AM
You could put something like this into the OnPrint event of your workbook:
Worksheets("sheet2").PageSetup.PrintArea = "a1:b" & Worksheets("sheet1").Range("a6000").End(xlUp).Row
Assuming your users are entering the data into Column A of Sheet1, this will adjust the print area of sheet2 to print the first 2 columns of sheet2 down to the last row with data entered in column A on sheet1.
Posted by Bill on August 24, 2001 10:04 AM
Uh... okay. I'm a newby at most of this. Much of what I have learned has been through help screens. I have no idea what an "onprint" event is, or where to find it, or how to handle it. Could you elaborate, if it's not too much trouble?
Thanks.
Bill.
Posted by cpod on August 24, 2001 10:36 AM
Sorry. What I gave you was a line of Visual Basic code that has to be pasted into the VB editor.
1 Go to the VB editor - Right click on the Excel icon that's just to the left of "File" on the menu. Choose "View Code" from the right click menu.
2 You will see a text box on the left that says "(General)". Open the textbox's dropdown list and choose "Workbook".
3 Open the dropdown list of the text box to the right of the last textbox and choose "BeforePrint".
4 Paste the line of code.
This puts the code in the Before Print event and it will run and adjust the print area each time the sheet is printed.
If you have any trouble let me know and I will send you a sample workbook.
Posted by Bill on August 24, 2001 12:10 PM
Coool!!
I had to modify the code you gave me a little (I had forgotten about the formulae on the 1st sheet that go on forever too!), but I was able to piece together the right commands from what you gave me!! Wow! Thanks so much.
Bill.
