HIDE UNHIDE automatically in others sheets

[chingilou]

hello excelurs
I have if you allow me an example file with 4 sheets
I only work on the first sheet !!
the data of the other 3 sheets is copied from sheet 1
and I print the 3 sheets from sheet1 also
I am looking for an automatic procedure to hide the empty lines of the 3 sheets
I tested 2 macro that the forumers suggested to me but saw the quantity of data it takes time to refresh
can you please suggest me a better method

[p45cal]

In the attached I've only changed anything for the printing of sheet Hanifatoys1, I leave you to do the rest.
What I've done:
1. Added formulae to the Hanifatoys1 sheet in range A22:E208
2. Changed the formula in cell E209 (to sum only visible cells)
3. Added text into cells A209:A211 and made it white to make it invisible (this is because writing the code only to autofilter range A21:A208, it insists on autofiltering all the way down to A211)
4. Removed all code from that sheet's code-module
5. Edited the print1 sub to apply the autofilter to column A (and hide the dropdown arrow) and print preview - nothing else needed

I hope it's fast enough.

[chingilou]

thank you, friend
I test and I will tell you if it is better

[p45cal]

I test and I will tell you if it is betterand ?

[chingilou]

sorry i was traveling so practically disconnected
the sheets 2,3,4 will be saved as a separate file so I would have liked no link

[p45cal]

A different approach for ATERNAL.

[chingilou]

good i appreciate ,thank you very much

[chingilou]

sorry but i made a mistake i didn't notice that the macro doesn't work at all notice the aternal sheet to the right of the data has appeared and if i delete them the macro stops
and if possible add 2 conditions
1 - NbLig = Application.CountIf (.Range ("A10: A196"), "> 0")
If NbLig> = 76 Then NbLig = 120 Else If NbLig <= 30 Then NbLig = 30 Else NbLig = 75
'''''to let some blanks for an acceptable rendering
2 - With ActiveSheet.PageSetup
If NbLig> 40 Then .CenterFooter = "Page & P of & N" Else .CenterFooter = ""
End With
Worksheets (Name) .PrintPreview
''''add pagination if row exceeds 40 lines

[p45cal]

notice the aternal sheet to the right of the data has appeared and if i delete them the macro stops
Sorry, I left my experimentation in. You can delete everything from column I to the right, but do NOT delete what's in cells H1:H2.
Nor delete what's in hidden row 13. Both of these are required for Advanced Filter.

I also left Module1 in which can be deleted.


and if possible add 2 conditions
1 - NbLig = Application.CountIf (.Range ("A10: A196"), "> 0")
If NbLig> = 76 Then NbLig = 120 Else If NbLig <= 30 Then NbLig = 30 Else NbLig = 75
'''''to let some blanks for an acceptable rendering
I don't understand the above at all.


2 - With ActiveSheet.PageSetup
If NbLig> 40 Then .CenterFooter = "Page & P of & N" Else .CenterFooter = ""
End With
Worksheets (Name) .PrintPreview
''''add pagination if row exceeds 40 lines

You can adjust the following on the ATERNAL sheet's Page Layout:

Set the bottom margin to be larger
Select colums A:E and Set the Print Area or in the code:
.PageSetup.PrintArea = "$A:$E"

To change the footer according to number of pages being printed:
If .HPageBreaks.Count > 0 Then .PageSetup.CenterFooter = "Page &P of &N" Else .PageSetup.CenterFooter = ""
Both these have been included in:
Sub print2()
With Sheets("ATERNAL")
Sheets("Acceuil").Range("A9:F196").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("H1:H2"), CopyToRange:=.Range("A13:D200"), Unique:=False
.Range("A13:A200").AutoFilter Field:=1, Criteria1:="<>", Visibledropdown:=False
.Range("A13:D200").Interior.ColorIndex = xlNone
.Range("A13:D200").Font.ColorIndex = xlAutomatic
.PageSetup.PrintArea = "$A:$E"
If .HPageBreaks.Count > 0 Then .PageSetup.CenterFooter = "Page &P of &N" Else .PageSetup.CenterFooter = ""
.PrintPreview
End With
End Sub
I'll leave you to do the other sheets.

[chingilou]

there it works well,

(.HPageBreaks.Count) I totally forgot this function thanks

I would like to not hide all the empty lines but to leave a little bit so that the pro forma (hanifatoys1,aternal,arba) will be prettier
so that TOTAL HT, VAT ,TOTAL PRICE are at the bottom and not in the middle or at the top of printed page like photo https://ibb.co/MR0SrYg

thank you from your patient

[p45cal]

I would like to not hide all the empty lines but to leave a little bit so that the pro forma (hanifatoys1,aternal,arba) will be prettier
so that TOTAL HT, VAT ,TOTAL PRICE are at the bottom and not in the middle or at the top of printed pageUnlike Word, there isn't a vertical alignment property of the pagesetup, there's only VerticalCenter which can only be True/False so it's going to be convoluted to get the last page to have the totals at the bottom. You could mess around with some vba to adjust the heights of some rows until it just fits on the page, or if there's only a small number of rows on the last page, code so that the document prints fit to x-1 pages tall. Personally, I'd settle for .PageSetup.CenterVertically = True for the whole document.

[chingilou]

I'm not talking about pagesetup you misunderstood me I think
you hide the lines by (autofilter)
I was using a macro which calculates column number to retrieve in "acceuil"

NbLig = Application.CountIf(Sheets("Acceuil").Range("A10:A196"), ">0") ' Calculation of useful lines
If NbLig >= 76 Then NbLig = 120 Else If NbLig <= 30 Then NbLig = 30 Else NbLig = 75 'to let blank in the pro forma before total HT
Nom = ActiveSheet.Name ' Name of active sheet
NoCol = Application.Match(Nom, Sheets("Acceuil").[9:9], 0) ' Column number to retrieve in Acceuil
Deb = Application.Match("*quantité*", [D:D], 0) + 1 ' Start of table where to store
Fin = Application.Match("*TOTAL HT*", [D:D], 0) - 1 ' End of table
' Préparation feuille
Rows(Deb & ":" & Fin).Hidden = False ' All lines unmasked
Range("A" & Deb & ":E" & Fin).ClearContents
it works well but I had timelags
with autofilter can i add the desired (calculated with NbLig or something else) empty lines

[chingilou]

my actual file with little timelags
i hope that you will see what i want

[p45cal]

It takes less than a second to produce each of the 4 print previews; what sort of time savings are you looking for?
Is your printer on a network? That can sometimes slow down code.
You could try enclosing any .pagesetup lines with Application.PrintCommunication = False/True:

Application.PrintCommunication = False
With ActiveSheet.PageSetup
If NbLig > 40 Then .CenterFooter = "Page &P de &N" Else .CenterFooter = ""
End With
Application.PrintCommunication = True

[chingilou]

hi !
I will do what you advise me
at home it takes me more than a second and even more in my shop
I do not use an inline printer
w7 pro x64 office 2016 32bit 16gb ram at my place 4gb at the store
and I am thinking of using other macros so I try to optimize as much as possible
thanks