Macro with multiple Headers for multiple pages

A

amkkhan

Board Regular
Joined
Dec 11, 2021
Messages
75
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Here is the link to file. I the last sheet "Individual Result GT"
I want to get a macro that Generates a pdf or print with following properties
1) Row 1 to Row 7 header for all first 10 pages (7 Rows header)
a) out of very first these 10 pages I want only pages to be printed/converted to pdf that contain data (ignore Blank pages)
b) each page contains 5 Records (Information)
c) You can view Print Preview to view details
2) Row 259 to 266 (8 Rows Header) for page 11 and 12
3) No header for last/13th page

Print Tiles option in Excel is not helping me unfortunately :(
 
Sort by date Sort by votes
Hi amkkhan,

my bad - I have made changes to the code after having tested it in the workbook and without debugging it before posting. This should cause problems both i creating the PDF as well as deleting the sheets .


The code should look like this:
VBA Code: 
Sub MrE1223268_mod02()
'https://www.mrexcel.com/board/threads/macro-with-multiple-headers-for-multiple-pages.1223268/
'change of range for printout from A:T to A:AF
'changing from Landscape to Portrait
'excluding a range from the printout
'excluding Header and Footer from scaling for the printout
'showing PDF after creation, no MsgBox shown
'no information about data for the first sheets so this is only a guess of how manual pagebreaks are set
'
'swapping of codelines to include a command into the With-clause
Dim lngPrint      As Long
Dim rngLast       As Range
Dim PDFsheets     As String
Dim ws            As Worksheet

Const cstrShData As String = "Individual Result GT"
Const cblnAlterSheetNumbers As Boolean = True

PDFsheets = ""
lngPrint = 1

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  Set rngLast = .Cells(258, "F").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":" & .Rows.Count).Delete
  Application.PrintCommunication = False
  With .PageSetup
    .PrintTitleRows = "$1:$7"
    .PrintTitleColumns = ""
  End With
  Application.PrintCommunication = True
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "F").End(xlUp).Row
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
lngPrint = lngPrint + ExecuteExcel4Macro("Get.document(50)")
Set ws = Nothing

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  Set rngLast = .Cells(331, "B").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":316").Delete
  .Rows("1:257").Delete
  Set rngLast = .Cells(58, "B").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":257").Delete
  .PageSetup.PrintArea = ""
  .ResetAllPageBreaks
  Application.PrintCommunication = False
  With .PageSetup
    .PrintTitleRows = "$1:$9"
    .PrintTitleColumns = ""
  End With
  Application.PrintCommunication = True
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "H").End(xlUp).Row
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
lngPrint = lngPrint + ExecuteExcel4Macro("Get.document(50)")
Set ws = Nothing

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  .Rows("1:331").Delete
  .PageSetup.PrintArea = ""
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "H").End(xlUp).Row
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
Set ws = Nothing
    
Worksheets(Split(Left(PDFsheets, Len(PDFsheets) - 1), ",")).Select

If cblnAlterSheetNumbers Then Application.Dialogs(xlDialogPageSetup).Show

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="C:\result\Result-" & Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) & ".pdf", _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=True, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=True

With Application
  .DisplayAlerts = False
  ActiveWindow.SelectedSheets.Delete
  .DisplayAlerts = True
  .ScreenUpdating = True
End With

End Sub

Sub amkkhanPageSetup(ws As Worksheet, _
                      cstrShData As String, _
                      lngPrint As Long)
With ws.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftHeader = cstrShData
  .CenterHeader = " "
  .RightHeader = "&P"
  .LeftFooter = "Class Incharge : ________________________________"
  .CenterFooter = "Controller of Exam : ________________________________"
  .RightFooter = "Principal : ________________________________"
  '/// left margin augmented
  .LeftMargin = Application.InchesToPoints(0.62992125984252)
  .RightMargin = Application.InchesToPoints(0.236220472440945)
  .TopMargin = Application.InchesToPoints(0.236220472440945)
  '/// bottom margin augmented
  .BottomMargin = Application.InchesToPoints(0.62992125984252)
  .HeaderMargin = Application.InchesToPoints(0.31496062992126)
  .FooterMargin = Application.InchesToPoints(0.31496062992126)
  .PrintQuality = 600
  '/// Header and Footer will be printed in normal size
  .ScaleWithDocHeaderFooter = False
  .CenterHorizontally = True
  .CenterVertically = True
  .Orientation = xlPortrait
  .Draft = False
  .PaperSize = xlPaperA4
  .FirstPageNumber = lngPrint
  '/// squeezing data to fit on one page
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = False
End With
End Sub

The code posted in #3 referred to ActiveSheet and for me deleted all newly copied sheets.

Holger
 
Upvote 0
Solution

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
HaHoBe said:
Hi amkkhan,

my bad - I have made changes to the code after having tested it in the workbook and without debugging it before posting. This should cause problems both i creating the PDF as well as deleting the sheets .


The code should look like this:
VBA Code: 
Sub MrE1223268_mod02()
'https://www.mrexcel.com/board/threads/macro-with-multiple-headers-for-multiple-pages.1223268/
'change of range for printout from A:T to A:AF
'changing from Landscape to Portrait
'excluding a range from the printout
'excluding Header and Footer from scaling for the printout
'showing PDF after creation, no MsgBox shown
'no information about data for the first sheets so this is only a guess of how manual pagebreaks are set
'
'swapping of codelines to include a command into the With-clause
Dim lngPrint      As Long
Dim rngLast       As Range
Dim PDFsheets     As String
Dim ws            As Worksheet

Const cstrShData As String = "Individual Result GT"
Const cblnAlterSheetNumbers As Boolean = True

PDFsheets = ""
lngPrint = 1

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  Set rngLast = .Cells(258, "F").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":" & .Rows.Count).Delete
  Application.PrintCommunication = False
  With .PageSetup
    .PrintTitleRows = "$1:$7"
    .PrintTitleColumns = ""
  End With
  Application.PrintCommunication = True
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "F").End(xlUp).Row
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
lngPrint = lngPrint + ExecuteExcel4Macro("Get.document(50)")
Set ws = Nothing

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  Set rngLast = .Cells(331, "B").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":316").Delete
  .Rows("1:257").Delete
  Set rngLast = .Cells(58, "B").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":257").Delete
  .PageSetup.PrintArea = ""
  .ResetAllPageBreaks
  Application.PrintCommunication = False
  With .PageSetup
    .PrintTitleRows = "$1:$9"
    .PrintTitleColumns = ""
  End With
  Application.PrintCommunication = True
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "H").End(xlUp).Row
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
lngPrint = lngPrint + ExecuteExcel4Macro("Get.document(50)")
Set ws = Nothing

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  .Rows("1:331").Delete
  .PageSetup.PrintArea = ""
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "H").End(xlUp).Row
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
Set ws = Nothing
  
Worksheets(Split(Left(PDFsheets, Len(PDFsheets) - 1), ",")).Select

If cblnAlterSheetNumbers Then Application.Dialogs(xlDialogPageSetup).Show

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="C:\result\Result-" & Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) & ".pdf", _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=True, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=True

With Application
  .DisplayAlerts = False
  ActiveWindow.SelectedSheets.Delete
  .DisplayAlerts = True
  .ScreenUpdating = True
End With

End Sub

Sub amkkhanPageSetup(ws As Worksheet, _
                      cstrShData As String, _
                      lngPrint As Long)
With ws.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftHeader = cstrShData
  .CenterHeader = " "
  .RightHeader = "&P"
  .LeftFooter = "Class Incharge : ________________________________"
  .CenterFooter = "Controller of Exam : ________________________________"
  .RightFooter = "Principal : ________________________________"
  '/// left margin augmented
  .LeftMargin = Application.InchesToPoints(0.62992125984252)
  .RightMargin = Application.InchesToPoints(0.236220472440945)
  .TopMargin = Application.InchesToPoints(0.236220472440945)
  '/// bottom margin augmented
  .BottomMargin = Application.InchesToPoints(0.62992125984252)
  .HeaderMargin = Application.InchesToPoints(0.31496062992126)
  .FooterMargin = Application.InchesToPoints(0.31496062992126)
  .PrintQuality = 600
  '/// Header and Footer will be printed in normal size
  .ScaleWithDocHeaderFooter = False
  .CenterHorizontally = True
  .CenterVertically = True
  .Orientation = xlPortrait
  .Draft = False
  .PaperSize = xlPaperA4
  .FirstPageNumber = lngPrint
  '/// squeezing data to fit on one page
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = False
End With
End Sub

The code posted in #3 referred to ActiveSheet and for me deleted all newly copied sheets.

Holger
Click to expand...
Hi Holger thanks :) it worked fine. God bless you
In #6, I requested
"The page Orientation should be Horizontal in the entire document."

although it was working fine in your Code at #3 but now you changed Orientation to Portrait.
please guide me how can I change it.

and one request please

Once when we are done with very first pages that have 7 lines header and proceed to lower part
the lower part should have a Maximum of 25 rows and the rest of rows go to next page please
I am saying about the pages that are single records (please let me know if you don't understand


and the very last page of document is fine. No changes required for the last page
 
Upvote 0
Hi amkkhan,

in procedure amkkhanPageSetup it's the codeline
VBA Code: 
  .Orientation = xlPortrait
which you should change to read
VBA Code: 
  .Orientation = xlLandscape

Regarding the request for change of layout for second part formatting the full code may look like this:

VBA Code: 
Sub MrE1223268_mod03()
'https://www.mrexcel.com/board/threads/macro-with-multiple-headers-for-multiple-pages.1223268/
'change of range for printout from A:T to A:AF
'changing from Landscape to Portrait
'excluding a range from the printout
'excluding Header and Footer from scaling for the printout
'showing PDF after creation, no MsgBox shown
'no information about data for the first sheets so this is only a guess of how manual pagebreaks are set
'
'swapping of codelines to include a command into the With-clause
'
'change from xlPortrait to xlLandscape in proc amkkhanPageSetup
'printing for second sheet has been altered: if up to 25 items one page,
'   number of items between 26 and 30 manual pagebreak to have 20 and rest on follow-up,
'   pagebreak at row 34 (25 + 9 for header) for full first page and rest on follow-up
Dim lngPrint      As Long
Dim rngLast       As Range
Dim PDFsheets     As String
Dim ws            As Worksheet

Const cstrShData As String = "Individual Result GT"
Const cblnAlterSheetNumbers As Boolean = True

PDFsheets = ""
lngPrint = 1

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  Set rngLast = .Cells(258, "F").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":" & .Rows.Count).Delete
  Application.PrintCommunication = False
  With .PageSetup
    .PrintTitleRows = "$1:$7"
    .PrintTitleColumns = ""
  End With
  Application.PrintCommunication = True
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "F").End(xlUp).Row
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
lngPrint = lngPrint + ExecuteExcel4Macro("Get.document(50)")
Set ws = Nothing

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  Set rngLast = .Cells(331, "B").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":316").Delete
  .Rows("1:257").Delete
  Set rngLast = .Cells(58, "B").End(xlUp).Offset(1, 0)
  .Rows(rngLast.Row & ":257").Delete
  .PageSetup.PrintArea = ""
  .ResetAllPageBreaks
  Application.PrintCommunication = False
  With .PageSetup
    .PrintTitleRows = "$1:$9"
    .PrintTitleColumns = ""
  End With
  Application.PrintCommunication = True
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "H").End(xlUp).Row
  '/// add a manual pagebreak if needed to acoid single records on follow-up page
  Select Case .Cells(258, "H").End(xlUp).Row
    Case Is <= 25
      'nothing to do, fits fine
    Case 26 To 30
      '/// printing 20 items on first page, rest on follow-up
      .HPageBreaks.Add Before:=.Range("A30")
    Case Is > 30
      .HPageBreaks.Add Before:=.Range("A35")
  End Select
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
lngPrint = lngPrint + ExecuteExcel4Macro("Get.document(50)")
Set ws = Nothing

Sheets(cstrShData).Copy After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
'/// start swapping codelines
With ws
  PDFsheets = PDFsheets & .Name & ","
'/// end swapping codelines
  With .UsedRange
    .Value = .Value
  End With
  .Rows("1:331").Delete
  .PageSetup.PrintArea = ""
  .PageSetup.PrintArea = "$A$1:$AF$" & .Cells(258, "H").End(xlUp).Row
  Application.PrintCommunication = False
  amkkhanPageSetup ws, cstrShData, lngPrint
  Application.PrintCommunication = True
  Set rngLast = Nothing
End With
Set ws = Nothing
    
Worksheets(Split(Left(PDFsheets, Len(PDFsheets) - 1), ",")).Select

If cblnAlterSheetNumbers Then Application.Dialogs(xlDialogPageSetup).Show

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="C:\result\Result-" & Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1) & ".pdf", _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=True, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=True

With Application
  .DisplayAlerts = False
  ActiveWindow.SelectedSheets.Delete
  .DisplayAlerts = True
  .ScreenUpdating = True
End With

End Sub

Sub amkkhanPageSetup(ws As Worksheet, _
                      cstrShData As String, _
                      lngPrint As Long)
With ws.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftHeader = cstrShData
  .CenterHeader = " "
  .RightHeader = "&P"
  .LeftFooter = "Class Incharge : ________________________________"
  .CenterFooter = "Controller of Exam : ________________________________"
  .RightFooter = "Principal : ________________________________"
  '/// left margin augmented
  .LeftMargin = Application.InchesToPoints(0.62992125984252)
  .RightMargin = Application.InchesToPoints(0.236220472440945)
  .TopMargin = Application.InchesToPoints(0.236220472440945)
  '/// bottom margin augmented
  .BottomMargin = Application.InchesToPoints(0.62992125984252)
  .HeaderMargin = Application.InchesToPoints(0.31496062992126)
  .FooterMargin = Application.InchesToPoints(0.31496062992126)
  .PrintQuality = 600
  '/// Header and Footer will be printed in normal size
  .ScaleWithDocHeaderFooter = False
  .CenterHorizontally = True
  .CenterVertically = True
  .Orientation = xlLandscape
  .Draft = False
  .PaperSize = xlPaperA4
  .FirstPageNumber = lngPrint
  '/// squeezing data to fit on one page
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = False
End With
End Sub

Holger
 
Upvote 0
grrrhhh...

Having an idea is one thing, writing proper code for that is something I should have learned in the meantime but ...

VBA Code: 
  '/// add a manual pagebreak if needed to acoid single records on follow-up page
  Select Case .Cells(258, "H").End(xlUp).Row
    Case Is <= 25
      'nothing to do, fits fine
    Case 26 To 30
      '/// printing 20 items on first page, rest on follow-up
      .HPageBreaks.Add Before:=.Range("A30")
    Case Is > 30
      .HPageBreaks.Add Before:=.Range("A35")
  End Select
is a just neglecting the 9 rows for the header so the code to take care of how to handle this should look like
VBA Code: 
  '/// add a manual pagebreak if needed to acoid single records on follow-up page
  Select Case .Cells(258, "H").End(xlUp).Row
    Case Is <= 34
      'nothing to do, fits fine
    Case 35 To 39
      '/// printing 20 items on first page, rest on follow-up
      .HPageBreaks.Add Before:=.Range("A30")
    Case Is > 39
      .HPageBreaks.Add Before:=.Range("A35")
  End Select

Holger
 
Upvote 0
HaHoBe said:
grrrhhh...

Having an idea is one thing, writing proper code for that is something I should have learned in the meantime but ...

VBA Code: 
  '/// add a manual pagebreak if needed to acoid single records on follow-up page
  Select Case .Cells(258, "H").End(xlUp).Row
    Case Is <= 25
      'nothing to do, fits fine
    Case 26 To 30
      '/// printing 20 items on first page, rest on follow-up
      .HPageBreaks.Add Before:=.Range("A30")
    Case Is > 30
      .HPageBreaks.Add Before:=.Range("A35")
  End Select
is a just neglecting the 9 rows for the header so the code to take care of how to handle this should look like
VBA Code: 
  '/// add a manual pagebreak if needed to acoid single records on follow-up page
  Select Case .Cells(258, "H").End(xlUp).Row
    Case Is <= 34
      'nothing to do, fits fine
    Case 35 To 39
      '/// printing 20 items on first page, rest on follow-up
      .HPageBreaks.Add Before:=.Range("A30")
    Case Is > 39
      .HPageBreaks.Add Before:=.Range("A35")
  End Select

Holger
Click to expand...
Thanks Holger. You are so cool, and I really appreciate your expertise.
My issue resolved and the code you shared is working perfectly
 
Upvote 0
HaHoBe said:
grrrhhh...

Having an idea is one thing, writing proper code for that is something I should have learned in the meantime but ...

VBA Code: 
  '/// add a manual pagebreak if needed to acoid single records on follow-up page
  Select Case .Cells(258, "H").End(xlUp).Row
    Case Is <= 25
      'nothing to do, fits fine
    Case 26 To 30
      '/// printing 20 items on first page, rest on follow-up
      .HPageBreaks.Add Before:=.Range("A30")
    Case Is > 30
      .HPageBreaks.Add Before:=.Range("A35")
  End Select
is a just neglecting the 9 rows for the header so the code to take care of how to handle this should look like
VBA Code: 
  '/// add a manual pagebreak if needed to acoid single records on follow-up page
  Select Case .Cells(258, "H").End(xlUp).Row
    Case Is <= 34
      'nothing to do, fits fine
    Case 35 To 39
      '/// printing 20 items on first page, rest on follow-up
      .HPageBreaks.Add Before:=.Range("A30")
    Case Is > 39
      .HPageBreaks.Add Before:=.Range("A35")
  End Select

Holger
Click to expand...
Actually your #13 worked perfectly fine for my available results.
I really did not ran #14 code.
can you explain that bit better and what is the purpose of both code chunks in #14 please
 
Upvote 0
Hi @HaHoBe I wish I were not bothering you for small queries when my issue is resolved once but I am just asking for information.
Is it possible by any chance if we want to reduce margins. Because in my case pdf file is occupying a lot of space that is being wasted.
I request you to have a look on attached screenshots. Screenshots 1 and 2

And What if we put page numbering at the bottom and reduce top Margins
Please Also brief me about Screenshots 3 and 4
 

Attachments

  • Margins.jpg
    Margins.jpg
    57.6 KB · Views: 3
  • Page Margins.jpg
    Page Margins.jpg
    106.2 KB · Views: 3
  • Top Section.jpg
    Top Section.jpg
    59.2 KB · Views: 4
  • Top Section_1.jpg
    Top Section_1.jpg
    88.7 KB · Views: 3
Upvote 0
Hi amkkhan,

regarding codes in #14 (as laid down in the comments at the start of the procedure and inside the Select Case): getting the number of rows on that particular sheet and decide regarding the number of entries like
  • last used row is less than or 34 (25 data + 9 for header): no pagebreak added
  • last used row is 35 to 39 (26 to 30 data entries): setting a manual pagebreak after 20 entries and getting the last on the second page
  • last used row is more than 39: manual pagebreak added after 25 data entries

regarding #17:
  1. If we move the page numbers down you should tell me where to put them as all 3 footers were filled when I downloaded the workbook. As you want page numbers there you should make a decision as what should happen with the original footers.
  2. Setting other margins or scaling for the printout: showing an image as what is won't help me to find out what you want. Taking picture #1: this is set to be 1 page wide like all the other pages. How should that look like? Picture #2: do what there?
  3. Pictures #3 and #4: left header shows the name of the sheet that is being transferred into three copies for different setting for output. And I'm afraid my coding says that the center header is only a blank:
    Rich (BB code): 
    With ws.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftHeader = cstrShData
  .CenterHeader = " "
  .RightHeader = "&P"
Holger
 
Upvote 0
HaHoBe said:
Hi amkkhan,

regarding codes in #14 (as laid down in the comments at the start of the procedure and inside the Select Case): getting the number of rows on that particular sheet and decide regarding the number of entries like
  • last used row is less than or 34 (25 data + 9 for header): no pagebreak added
  • last used row is 35 to 39 (26 to 30 data entries): setting a manual pagebreak after 20 entries and getting the last on the second page
  • last used row is more than 39: manual pagebreak added after 25 data entries

regarding #17:
  1. If we move the page numbers down you should tell me where to put them as all 3 footers were filled when I downloaded the workbook. As you want page numbers there you should make a decision as what should happen with the original footers.
  2. Setting other margins or scaling for the printout: showing an image as what is won't help me to find out what you want. Taking picture #1: this is set to be 1 page wide like all the other pages. How should that look like? Picture #2: do what there?
  3. Pictures #3 and #4: left header shows the name of the sheet that is being transferred into three copies for different setting for output. And I'm afraid my coding says that the center header is only a blank:
    Rich (BB code): 
    With ws.PageSetup
  .LeftHeader = ""
.CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  .LeftHeader = cstrShData
.CenterHeader = " "
  .RightHeader = "&P"
Holger
Click to expand...
hi Holger
I was just saying about margins see the image.
I meant if we minimize top and bottom spaces (red Highlighted) then there would be extra space so we can increase the row height a bit to make it easier for the viewer when pdf is printed on paper. I don't want more then 5 students per page, but we can bit expand vertically if top and bottom spaces are not there

Margins - Copy.jpg
 
Upvote 0
Hi amkkhan,

we set the area up to fit on one sheet wide - I doubt changing header and footer margins to have any influence on how wide the sheet is in order to change the row height (which is automatic to fit the requirement of one sheet wide for all data to display). Or shorter: you can have a display of 5 data sets per page as is or change that to something else but that could mean that less than 5 data sets are displayed or that more than one page wide would be needed (I might be wrong here but that is what I remember from that subject).

And regarding the 10 displayed: it may be the number of pages inside the document.

Holger
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA for setting up the printing pages per sheet
Replies
0
Views
494
Aurum1
A
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
305
John_w
J
B
  • Question Question
VBA exports worksheet from XLSM to XLXS matching all source properties doesn't keep print area
Replies
0
Views
100
brentcole
B
D
  • Question Question
VBA Multiple "conditional" page breaks
Replies
3
Views
1K
Domenic
D
A
  • Question Question
Print Tiles for Limited Pages
Replies
1
Views
413
John_w
J

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top