How to automate printing in this example ?

[khelgadi]

Dear Excel Gurus,
Thanks for your support every time I need!

I'm in need of printing reports as given info in the attached file.
- I select the product on sheet 'Main' and depending on that lot of data has to be printed on Pg Nos 1 to 5.
- Depending on prod cat, I need to print only those pages where I have written 'Yes' .
- Pg no 2-5 are set for A4 and does have fixed no of rows and columns.
- Pg no 1 contains ingredient list, which may have 1 to 60 entries as given in 'Prod Master' Sheet. Hence, the no of rows are variable. I want this sheet as well to be printed on A4 size.
-I also need to have preview of all pages before printing.

Attaching the file here. Let me know if I've missed in condition.
Will be curious and thankful for answer....

 

[Hui]

Have a look at the file attached:

 

[khelgadi]

Dear Hui,
I'm yet to apply the same in my actual file... , but thanks for saving my life, as usual !!!
I'll get back to you once I do that.

 

[khelgadi]

Yup, the solution works! Thanks
However , I still need one more thing....

(1) As mentioned, Page 1 contains the list of ingredients, coz of which the length of the page varies. In any case the page has to be printed on A4, so how is that possible by using this macro ?

As I'll move ahead on this project, I may even need further help...
Thanks once again for the help

 

[Hui]

Change teh code to that below

Sub Print_Macro()
Dim c As Range

For Each c In Worksheets("Main").Range("H4:L4")
  If c.Text = "Yes" Then
  Sheets(c.Offset(-1).Value).Select

  With ActiveSheet.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftMargin = Application.InchesToPoints(0.275590551181102)
  .RightMargin = Application.InchesToPoints(0.275590551181102)
  .TopMargin = Application.InchesToPoints(0.196850393700787)
  .BottomMargin = Application.InchesToPoints(0.15748031496063)
  .HeaderMargin = Application.InchesToPoints(0.196850393700787)
  .FooterMargin = Application.InchesToPoints(0.15748031496063)
  .PrintHeadings = False
  .PrintGridlines = False
  .PrintComments = xlPrintNoComments
  .PrintQuality = 600
  .CenterHorizontally = True
  .CenterVertically = True
  .Orientation = xlPortrait
  .Draft = False
  .PaperSize = xlPaperA4 'Here tis
   .FirstPageNumber = xlAutomatic
  .Order = xlDownThenOver
  .BlackAndWhite = False
  .Zoom = 100
  .PrintErrors = xlPrintErrorsDisplayed
  .OddAndEvenPagesHeaderFooter = False
  .DifferentFirstPageHeaderFooter = False
  .ScaleWithDocHeaderFooter = True
  .AlignMarginsHeaderFooter = True
  .EvenPage.LeftHeader.Text = ""
  .EvenPage.CenterHeader.Text = ""
  .EvenPage.RightHeader.Text = ""
  .EvenPage.LeftFooter.Text = ""
  .EvenPage.CenterFooter.Text = ""
  .EvenPage.RightFooter.Text = ""
  .FirstPage.LeftHeader.Text = ""
  .FirstPage.CenterHeader.Text = ""
  .FirstPage.RightHeader.Text = ""
  .FirstPage.LeftFooter.Text = ""
  .FirstPage.CenterFooter.Text = ""
  .FirstPage.RightFooter.Text = ""
  End With
  ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
  End If
Next

End Sub

You can remove a lot of the other lines except the .PaperSize = xlPaperA4 line
But you can also see how you can customise those parameters as well

 

[khelgadi]

Dear Hui,
Thanks for the code, there's lot to learn from it. I tried it but still getting problem.
In the attached file, if you change the product from PRoduct 2 ( few ingredients) to product 8 ( 50+) , the print out should get adjusted to single A4 page, but its not. Please help.

 

[Hui]

Why don't you unhide Rows 40:71 on Pg1 and leave them visible or do you want that done automatically

 

[khelgadi]

As you can see in this file, The list depends on the number of products. If I keep all rows unhidden, the print comes with a very small font size, only for 10% products, the ingredients are more than 30, then is required to print with extra lines.
Can you please do it automatically?