macro to define a print area Solved

Question

Bonjour,  I have a data table with 16 columns and x rows. The first 12 rows contain general information about the table. The data starts from row 13 up to x.  I would like to have a button that formats the layout so that there are at most 15 lines of data on one page; if there are more than 15 lines, it should print on another page, etc.  If there are additional pages printed, I would like rows 5 to 12 to be printed at the top of each sheet as well.  Is this possible?  Thank you.

Le Pingou · Answer

Hello, Why not use the [ZoneImpr] tool directly in the [Page layout] tab.... !   -- Regards. The Penguin

marc41000 · Answer

Good evening to avoid manipulation and for this file to be used by multiple people

Le Pingou · Answer

Hello, It is not a macro that will prevent manipulations by another user (what do you do if they modify the columns ....?). You can set the print area as I suggested and that’s it, there’s nothing users need to do except print the sheet.   -- Best regards. The Penguin

marc41000 · Answer

Hello  The number of columns will not change, it's just the number of rows that varies from say 20 to x.  With your method, it is necessary to define the print area each time with operations that are a bit complicated for a fragile user.  There should not be a table with empty rows when printing, hence the idea of the print area macro.  Have a nice day.

Le Pingou · Answer

Hello, I misunderstood your problem. You can resolve it by setting the page margins, see example: https://www.cjoint.com/?3Jyv33102Hz   -- Best regards. The Penguin

marc41000 · Answer

Hello, let me explain again: Hello  My table has 16 columns and x rows. The first 12 rows contain general information about the table. The data starts from row 13 to x rows.  I would like to have a button next to the table that will format the page as follows: - On the first page, the first 12 rows (general information) + 15 rows of data. - On page 2, rows 5 to 12 + the remaining data rows. - .... If there are additional printed pages, I would like rows 5 to 12 to also be printed at the top of each sheet.  Is this possible?  Thank you.

Le Pingou · Answer

Hello, The example I proposed does exactly what you want without macros; you just need to adapt it according to your data format.   -- Best regards. The Penguin

marc41000 · Answer

Good evening  I do not understand your Excel file  Best regards

Le Pingou · Answer

Hello, Have you tried previewing before printing or printing the page as well as removing data rows...?   -- Regards. The Penguin

marc41000 · Answer

Hello   by removing the lines it works but I don't want people using this table to delete or add lines because some cells contain formulas, conditional formatting ...   so I would like a simple action to format the page and define a print area, that's why I was thinking of a macro but maybe there is an easier way but right now I don't know how to do it   thank you for your help

Le Pingou · Answer

Hello, Well, in that case, what is the structure of your sheet and where are users allowed to enter data.... ?   -- Best regards. The Penguin

Maurice · Answer

Hello  Here is a macro for Row & Column format  Sub Macro2()  With ActiveSheet.PageSetup  .PrintTitleRows = "$5:$12" ' Row Selection  .PrintArea = "$B:$G" ' Column Selection  End With End Sub  Best Maurice

marc41000 · Answer

Good evening  As I mentioned, users can enter data in the table rows, and the number of rows varies greatly from day to day, ranging from 15 rows to over 100  This table is already designed, and the cells already have formulas  Please understand that I do not want to print a table with blank rows; that's why the print area needs to be defined each time.

Le Pingou · Answer

Hello, Well, that's clearer now, so we only need to consider the lines that the user will have entered. I imagine that the first column contains a formula... What is that formula...? The format of the sheet: landscape or portrait?  Regards. The Penguin

Le Pingou · Answer

Hello, Admitted column 2 [B] is filled in by the user and serves as a reference for the procedure below: Sub DefinePrintArea()' search for last row in column [B]With ActiveSheet.PageSetup .PrintTitleRows = "$5:$12" .PrintArea = ("$A$1:$S$" & Cells(Rows.Count, 2).End(xlUp).Row)End WithEnd Sub  -- Best regards. The Penguin

marc41000 · Answer

re  in landscape mode column 16 contains the calculations, the data is entered in a line from column 1 to 15

Le Pingou · Answer

Hello, Thank you for the information. We use column [A] to detect the print range. Here is the procedure to insert into a module: Sub DefinirZoneImpr()' search for the last row in column [A:A]With ActiveSheet.PageSetup .PrintTitleRows = "$5:$12" .PrintArea = ("$A$1:$S$" & Cells(Rows.Count, 1).End(xlUp).Row)End WithEnd Sub To trigger it, place a command button on the sheet and give it a reference to the procedure. The print order is not included in the code.   -- Best regards. The Penguin

marc41000 · Answer

Hello  to summarize what I need to write in the complete macro that I am trying  thank you again for your help  Regards

Le Pingou · Answer

Hello, Simply the command ActiveSheet.PrintOut Insert between [End With] and [End Sub]   Best regards. The Penguin

marc41000 · Answer

Good evening  In summary, should I include this?  Sub DefinePrintArea() ' search for the last row in column [B] With ActiveSheet.PageSetup  .PrintTitleRows = "$5:$12"  .PrintArea = ("$A$1:$S$" & Cells(Rows.Count, 2).End(xlUp).Row) End With ActiveSheet.PrintOut  End Sub

Le Pingou · Answer

Hello,
Yes, with one detail: according to your information: column 16 contains the calculations and the data is entered in a row from column 1 to 15
I took column 1 or [A] instead of 2
So the procedure is:

Sub DefinePrintArea()
' search for the last row in column [A:A]
With ActiveSheet.PageSetup
 .PrintTitleRows = "$5:$12"
 .PrintArea = ("$A$1:$S$" & Cells(Rows.Count, 1).End(xlUp).Row)
End With
ActiveSheet.PrintOut
End Sub

--
Best regards.
The Penguin

marc41000 · Answer

Thank you, I will try this this weekend  thanks again

marc41000 · Answer

Hello  thank you very much, it works  I have a request from a user, is it possible for the button to not trigger the print but only to define the print area  best regards

Le Pingou · Answer

Hello, The best thing is to insert a printed yes or no choice into the procedure, and that way you have just one button. This should be achievable for you.  -- Regards. The Penguin

marc41000 · Answer

RE  let go of the print button yes or no the simplest way as I am asked is to define the print area  Regards

Le Pingou · Answer

Hello, Start by trying what I proposed to you and then you come back....!  -- Regards. The Penguin

marc41000 · Answer

Users prefer to print themselves  Best regards

Le Pingou · Answer

Hello, Thank you, this is really nonsense...! I am leaving the position.   -- Regards. The Penguin

marc41000 · Answer

Why is it nonsense?

Macro to define a print area - Page 2

Unable to sign in to canva with outlook email

Pixma ts printer is broken

White square on my desktop

Photos not saving on onedrive and cloud

How to plug a usb cord into a tv

How to send an email to ebay?

Dictation voice

Computer stuck at boot menu on startup page

Connect a xiaomi redmi 9t to a pc

Windows 7 photo viewer update