Solved How to insert a different Header Line 2 for each print page?

June 16, 2020 at 11:37:49
Specs: Windows 10
I have a worksheet with all Sales Reps listed with multiple lines of data for each rep (invoice no., customer name, item description, etc.), in columns (A:I) which are all selected as the print area. In column M, the word Total appears beside each reps total amount of sales which is in column L. His name appears in column N. I have the vba to loop through the sheet and set page breaks after each row with the word "Total" in column M. I also have the VBA to print all reports but I need to put the salesman's name from column N in the Header for his specific page before it prints. Can this be done with VBA? The only answer I have found so far would be to place the same Header from a single cell value instead of selecting a different Header line based upon multiple cell values at the start of a page. Any ideas would be greatly appreciated.

See More: How to insert a different Header Line 2 for each print page?

Reply ↓  Report •

✔ Best Answer
June 18, 2020 at 19:13:57
In your OP you said:

"I also have the VBA to print all reports but I need to put the salesman's name from column N in the Header for his specific page before it prints."

There are no names in Column N. All of the names appear in Column M. I can only work with the information I am given.

In your OP, you said:

"In column M, the word Total appears beside each reps total amount of sales which is in column L."

You failed to mention that the word Total appeared along with the rep's name and was put in the cell by a formula. i.e. it wasn't simply the text string "Total".

You may not have realized how important those 2 facts are, but when it comes to VBA every detail matters.

Before I post a revised version of the code, I have another comment. It appears that all the "1"s in Column S are simply place holders so that your code can count to 500. That is inefficient and somewhat sloppy. It could also cause an issue if your spreadsheet ever exceeds 500 rows. You (assuming it's still you when that time comes) would need to edit the macro to make it work. It would better if you let the macro determine the last row with data and let your loop be something like:

For i = 5 To lastRw

A simple Google search will return lots of hits for using VBA to find the last row with data. By making the end of your loop dynamic, it can grow (or shrink) along with your data.

BTW...Do you know how to single-step through VBA code so you that you can debug it as well as reverse engineer it so that you can figure out what it is doing? Simply clicking Run and hoping for the best is a hard way to figure out why the any code isn't doing what you want it do. ;-)

Try this code:

Sub SetPrintAreas_V2()
Dim sRw As Long
Dim eRw As Range
Dim firstAddress As String
Dim ws As Worksheet

Set ws = Sheets("Individual Reports")

'Initialize start row variable for first Print Job
   sRw = 5
   
'Find "Total" - eRw.Row will be end row for Print Job
     With Columns("M")
       Set eRw = .Find("Total", Lookat:=xlPart, LookIn:=xlValues)
        If Not eRw Is Nothing Then
          firstAddress = eRw.Address
            Do
              
'Each time Total is found, build PrintArea, set LeftHeader
             Application.ScreenUpdating = False
              With ws.PageSetup
                 .PrintArea = "$A$" & sRw & ":$M$" & eRw.Row
                 .LeftHeader = Range("M" & sRw)
                End With
             Application.ScreenUpdating = True
'Preview Print Job. Change to .PrintOut to Print
                 ws.PrintPreview
              
'Set start row for next Print Job
             sRw = eRw.Row + 1

'FindNext Total, Loop until last one is found
             Set eRw = .FindNext(eRw)
            Loop While Not eRw Is Nothing And eRw.Address <> firstAddress
        End If
    End With
    
'Reset PrintArea and LeftHeader
      With ws.PageSetup
        .PrintArea = ""
        .LeftHeader = ""
      End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
June 16, 2020 at 19:48:02
Instead of using a single Print Area and setting page breaks after each Total row, try setting a specific Print Area for each sales person's data. Using that method, you should then be able to use the value in the proper Column N cell in the Header.

I can't test anything right now, but it might look something like this:

Sub DifferentHeader()
    Dim ws As Worksheet

NxtPrintArea:

'     VBA code to determine First and Last Row to Print

    myPrintArea = Whatever Range the above code figured out

    Application.ScreenUpdating = False
        With ws.PageSetup
         .PrintArea = myPrintArea 
         .LeftHeader = Range("N" & (row with current name))
        End With
        ws.PrintPreview
    Application.ScreenUpdating = True

    ws.PageSetup.PrintArea = ""

GoTo NxtPrintArea

'You figure out how to make it stop

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
June 16, 2020 at 20:39:19
I guess I am having some difficulty figuring out how one click of the button will result in some 25 reports being printed with the proper header for each, with each one being varying lengths when the reports are printed each month with this approach. I can set the first and last row to be printed overall, but don't know how to set the first and last rows within the body of the sheet when they are constantly changing in length. There could be 500 lines on the spreadsheet, but some reports will have a couple of lines of print and some may have 50 or more. The VBA code that sets the page breaks does so accurately based upon the value of "Total" being in column M where the page breaks occur. If there is a way to determine the first and last row of each report with VBA, I would certainly be willing to give it a try. Thank you for your kind response.

Reply ↓  Report •

#3
June 17, 2020 at 04:50:26
Perhaps seeing your current code would make it easier for me to explain how to set the print areas.

Obviously, you are able to find the last Row of each report since you appear to be able to find the word "Total" in Column M. Seems to me that the first line of each "next" report would start one or 2 rows below that and end at the next Total. Grab those Row numbers and use them to set each PrintArea, then print it. Keep looping through the code until you reach the last occurance of Total. Each Report will be a separate print job but they will all be done by the macro.

Not knowing your worksheet layout makes it difficult for me to be more specific.

If you record a macro while you set a specific PrintArea you'll get an idea of what the code should look like.

Perhaps posting some sample data along with your code would help. Please click on the link at the bottom of this post and read the instructions on how to post sample data and code. Make sure that the data does not contain any personal/confidential information.

I will be traveling today so won't be able to look at this until tonight/tomorrow.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
June 17, 2020 at 10:39:31
You have probably guessed by now that I am a novice when it comes to VBA. I pick up bits and pieces from the internet and copy portions that fit my needs. I presently have 2 different Macros that I will join together when I am finished solving the problems: One that inserts the page breaks and one that hides rows that do not need to print and then prints the individual reports in order and finally unhides the rows with only formulas in them but no values except a #1 in column S. The instructions don't guide me in how to attach a workbook, but I have an edited workbook that I will be happy to upload if you will tell me how. I am copying the two Macros I am using below. I tried to copy a page from the worksheet in this space, but it ends up all jumbled up. You are right in that the next page begins on the next row after "Total".

Sub InsertPageBreaks()

'This removes all existing page breaks (if any) re-enters correct page breaks

Dim TotalReports As Integer
Dim i As Long
Dim r As Long

TotalReports = Range("R1").Value
i = 2
r = 2

ActiveWindow.View = xlNormalView
ActiveSheet.Cells.PageBreak = xlPageBreakNone
Do While i <= TotalReports
If Cells(r, 18).Value = "Total" Then
Rows(r + 1).PageBreak = xlPageBreakManual
i = i + 1
End If
r = r + 1
Loop
End Sub

Sub Print_Sales_Reps_Commission_Reports()

' Print Macro for Reps Commission Reports
'Hide all formulas on blank rows with "1" in column "S" (to 500)

Dim i As Integer
Application.ScreenUpdating = False
For i = 5 To 500
If Sheets("Individual reports").Range("S" & i).Value = 1 And _
Sheets("Individual Reports").Range("S" & i).HasFormula Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next

'Print Reports
Application.ScreenUpdating = True

Columns("A:L").Select
Range("A1").Activate
Selection.PrintOut Copies:=1, Preview:=True, Collate:=True

'Unhide all columns and rows
Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False

End Sub


message edited by lgillman


Reply ↓  Report •

#5
June 17, 2020 at 19:20:22
It does not appear that you followed the instructions on how to post code (or data) in this forum.

re: "The instructions don't guide me in how to attach a workbook, but I have an edited workbook that I will be happy to upload if you will tell me how."

You cannot upload a workbook in this forum. You would need to upload it to a external site and then post the link here. Please do not use any site that requires the user to register or sign in because odds are that we won't.

Please click on the link at the bottom of this post and read the instructions on how to post sample data and code using the pre tags.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#6
June 18, 2020 at 07:51:06
This code seemed to work for me, but since I don't have your exact layout, it may need some modifications. It does not deal with hiding rows or columns, it just creates PrintAreas based on how I believe your worksheet is laid out.

Sub SetPrintAreas()
Dim sRw As Long
Dim eRw As Range
Dim firstAddress As String
Dim ws As Worksheet

Set ws = Sheets(1)

'Initial start row variable for first Print Job
   sRw = 1
   
'Find "Total" - eRw.Row will be end row for Print Job
     With Columns("M")
       Set eRw = .Find("Total")
        If Not eRw Is Nothing Then
          firstAddress = eRw.Address
            Do
              
'Each time Total is found, build PrintArea, set LeftHeader
                With ws.PageSetup
                 .PrintArea = "$A$" & sRw & ":$N$" & eRw.Row
                 .LeftHeader = Range("N" & sRw)
                End With
                
'Preview Print Job. Change to .PrintOut to Print
                 ws.PrintPreview
              
'Set start row for next Print Job
             sRw = eRw.Row + 1

'FindNext Total, Loop until last one is found
             Set eRw = .FindNext(eRw)
             Loop While Not eRw Is Nothing And eRw.Address <> firstAddress
        End If
    End With
    
'Reset PrintArea and LeftHeader
      With ws.PageSetup
        .PrintArea = ""
        .LeftHeader = ""
      End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#7
June 18, 2020 at 18:20:45
That didn't work for me. Below is a link to my excel file. i copied your code into Module 1 and when it didn't work, I changed some of the references to what I though it should be, but still couldn't get it to work. Thanks for all your help. I am sure you will say, why didn't you tell me that when you see the data. it is the "Individual Reports" worksheet that we are trying to get to print.

https://drive.google.com/file/d/1w5...


Reply ↓  Report •

#8
June 18, 2020 at 19:13:57
✔ Best Answer
In your OP you said:

"I also have the VBA to print all reports but I need to put the salesman's name from column N in the Header for his specific page before it prints."

There are no names in Column N. All of the names appear in Column M. I can only work with the information I am given.

In your OP, you said:

"In column M, the word Total appears beside each reps total amount of sales which is in column L."

You failed to mention that the word Total appeared along with the rep's name and was put in the cell by a formula. i.e. it wasn't simply the text string "Total".

You may not have realized how important those 2 facts are, but when it comes to VBA every detail matters.

Before I post a revised version of the code, I have another comment. It appears that all the "1"s in Column S are simply place holders so that your code can count to 500. That is inefficient and somewhat sloppy. It could also cause an issue if your spreadsheet ever exceeds 500 rows. You (assuming it's still you when that time comes) would need to edit the macro to make it work. It would better if you let the macro determine the last row with data and let your loop be something like:

For i = 5 To lastRw

A simple Google search will return lots of hits for using VBA to find the last row with data. By making the end of your loop dynamic, it can grow (or shrink) along with your data.

BTW...Do you know how to single-step through VBA code so you that you can debug it as well as reverse engineer it so that you can figure out what it is doing? Simply clicking Run and hoping for the best is a hard way to figure out why the any code isn't doing what you want it do. ;-)

Try this code:

Sub SetPrintAreas_V2()
Dim sRw As Long
Dim eRw As Range
Dim firstAddress As String
Dim ws As Worksheet

Set ws = Sheets("Individual Reports")

'Initialize start row variable for first Print Job
   sRw = 5
   
'Find "Total" - eRw.Row will be end row for Print Job
     With Columns("M")
       Set eRw = .Find("Total", Lookat:=xlPart, LookIn:=xlValues)
        If Not eRw Is Nothing Then
          firstAddress = eRw.Address
            Do
              
'Each time Total is found, build PrintArea, set LeftHeader
             Application.ScreenUpdating = False
              With ws.PageSetup
                 .PrintArea = "$A$" & sRw & ":$M$" & eRw.Row
                 .LeftHeader = Range("M" & sRw)
                End With
             Application.ScreenUpdating = True
'Preview Print Job. Change to .PrintOut to Print
                 ws.PrintPreview
              
'Set start row for next Print Job
             sRw = eRw.Row + 1

'FindNext Total, Loop until last one is found
             Set eRw = .FindNext(eRw)
            Loop While Not eRw Is Nothing And eRw.Address <> firstAddress
        End If
    End With
    
'Reset PrintArea and LeftHeader
      With ws.PageSetup
        .PrintArea = ""
        .LeftHeader = ""
      End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#9
June 19, 2020 at 20:23:06
Thank you so much. This worked perfectly. If there is a next time, I will have learned much from your comments. I am grateful.

Well, almost perfectly. I subsequently discovered the first report doesn't show the name of the Sales person at the top. All the others do.

message edited by lgillman


Reply ↓  Report •

#10
June 21, 2020 at 19:01:24
Got it to work. I had increased the Font on the salesman's name and moved the header to CenterHeader. Something I must have done wrong caused the first name to not print on the report, but I now have it working. Thanks again. You are the best!

Reply ↓  Report •

Ask Question