Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Print certain pages based on a cell's value

0

I've been trying to find a solution to this with no luck.  I don't know all that much about VBA/macros so that is probably where the answer lies.

I have a spreadsheet with 2 sheets. The 1st sheet has some input data (person's name, address, etc.) and the 2nd sheet has a large (19 page) packet that has that data interspersed within the pages.  

I want someone to be able to open this template, enter all the info needed on the 1st sheet, and hit print (printing on the 2nd sheet) without even needing to look at the 2nd sheet.  I have all the data populating properly my only problem is printing.

One of the input data points is their marital status (Single or Married).  If they are Single then I don't want pages 8 & 9 on the second sheet to print, otherwise all the pages can print.

Is there a way I can have a button the can click with a macro that prints those specific pages based on that cells value?  

Thanks for any help and let me know if I didn't make any sense in my question (sometimes that happens to me)!

Answer
Discuss

Answers

0

Generally speaking, it is very hard to identify printed pages in an Excel worksheet. That is because a spreadsheet is of practically indefinite length which must be devided into the finite lengths of paper pages where the defining factor is the row height which, in turn, is affected by the character size - and I didn't mention print margins yet.

The answer to your problem is to translate it into row numbers, and then do some smart formatting to ensure that the numbers you specify as wanted or unwanted end up representing the pages you print or omit.

With that said, it wouldn't be very hard to print, say, rows 932 to 1131, or omit those very rows from a general printout, all depending upon certain conditions created on your Sheet1. It would even be relatively easy to define those rows programmatically in some way should they be subject to moving within the sheet such as would happen when rows before them are inserted or deleted or themselves omitted.

Discuss

Discussion

Possible yes!  Not seeing your data~ makes it more difficult to get a good result. HOWEVER, you can put a BUTTON on the 1st tab to print results on 2nd tab.  For the second tab, it would be best to have the data in a TABLE format, then you can filter table and hide SINGLE and print remainder. Like V said, it is very difficult to print certain things. I was hoping by FILTERING out the data you don't want you can accomplish this (I may have it wrong filtering out the SINGLE data, Again the problem is identifying the info on the last 2 pages (8 & 9) that you don't want to print.  Is there a way to programatically identify those records that you do not want printed??
queue (rep: 467) Oct 20, '17 at 9:17 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login