VBA to highlight individual rows before printing

[Paul103]

Hi,
I have a worksheet where all the data is fed from another sheet via formulas. The data is randomly spread and has a fixed print area that amounts to 6 pages including the repeated rows at the top of each page. There is a series of macro's used to print varying numbers of copies of each page via a cell value in the print summary. The number of copies printed per page is calculated by data shown in column C. What I am trying to achieve is when printing starts it highlights in yellow the first row with data in column C (starting at C27 to C211) and then highlights the second row with data in column C for the second copy and so on until the last row with data in column C has been printed. So if there are seven copies printed out for page one each copy differs by the highlighted row. Please see attached sample workbook and the printing macro's are shown below. Being a relative novice to VBA I do not know where to begin with this problem or even if it can be done. Any help as usual is always greatly appreciated.

Please Login or Register  to view this content.

[Bernie Deitrick]

This will print one copy for each line with values - click the button.

Right now, the macro uses "printpreview" - modify that one line by changing the printpreview to printout when you are happy with the macro and the colors.

Here's the file

SamplePrint.xlsm

[Paul103]

Bernie, thankyou for your time and I think your code basically does all I require but I have confused you by colouring the background of all six pages in different colours. In the real world there is no background fill so all I need is to highlight each row in turn with color Yellow 27. As I said I am just a novice with VBA and am not quite sure if both your macros are now required. Could I please ask if you could spare me a little more of your time to modify the code or if easier just tell me what to do. Many thanks again

[Bernie Deitrick]

Replace both of the macros with this - after you have tested it, remove the printpreview as before. If you are applying this macro to another workbook other than the one I posted, you need to create the ranges Pages, Page1, ... Page6 to have the code work correctly.

Please Login or Register  to view this content.

[Paul103]

Bernie, your code works a treat but the page number I have set up in the page header does not work - it is stuck on page 1. If it is not possible to get this working with the way you have written the code could you write another line or so to put a page number in cell "L2". Sorry to be a pest, I really do appreciate you taking the time to help me out.....thanks.

[Bernie Deitrick]

This will put the page number into L2:

Please Login or Register  to view this content.

[Paul103]

Bernie, I thankyou for all your help today and I really do appreciate your fast responses. This evening I will study your code with a keen eye and try to see what is happening where and when. Slowly but surely I am learning a great deal from you VBA guru's. All the best you genius!

[Paul103]

Bernie, just a quick question regarding the above, in my actual workbook I have 27 worksheets so is there a quicker way I can create all the named ranges for each sheet other than doing it manually. All the worksheets are identical apart from where the information is picked up. Thanks

[Bernie Deitrick]

This will print any sheet that is active:

Please Login or Register  to view this content.

And this will print ALL sheets:

Please Login or Register  to view this content.

[Paul103]

Bernie, thanks a million you have just saved me hours of work and worry. The print active sheet is exactly what I needed and sorry to have made you respond so many times with all that code, but your help and near instant response has been most appreciated.

[Paul103]

Bernie, Is there a quick way to adapt the Sub "PrintActiveSheet" to just print out 1 copy of the sheets with data in in them - no highlighting, no background fill and page number in L2. This is to act as check sheets before printing loads of copies out. I have tried adapting with the code below but keeps printing out more than 1 copy.

Please Login or Register  to view this content.

[Bernie Deitrick]

Please Login or Register  to view this content.

[Paul103]

Thanks Bernie, just altered the line "For i = 1 To 6" to "For i = 1 To sh.Range("AK14").Value" and it works like a dream!