Text in every page footer

[adam]

Hi anyone,

In the attached document how could I make the texts colored in yellow cells to appear on every page’s footer with the page number when I print the sheet.

Instead of repeating the same yellow cells with the same text I just want the texts in yellow cells to appear at the end of the last sheet. But printed should appear on every page's footer.

Any help on this would be kindly appreciated.

I’ve attached the workbook for your reference.

Thanks in advance.

[adam]

Sorry my heading says date in every footer. Its not the footer but the text in yellow to be on every page

[adam]

Thanks Leif for the edition. I would be happy if anyone could let me know how to do what I've requested.

[Don Wells]

Hi Adam
    I don't think that it is feasible to place that data in the footer; however you might consider placing it in rows 1:5 and repeating these rows on every page.

[adam]

Thanks for the help Don. But I guess that wont help as I want those texts to appear on the footer. And as I want the texts from row 1:12 to appear on the repeating rows.

[sdckapr]

I don't have XL2007, but I would presume if it COULD do this, it would be in somethin like: Page setup - Sheet(tab)- Print Titles and repeat at bottom. (from XL97-XL2003 it only allows repeats at top and left). If that option is still not available the only options that come to mind:
1) Print the "Text-footer" on blank sheets of paper, then increase the bottom margin on your sheets and print to these pre-printed sheets
2) Create a picture file of the "text-footer" and add this graphic to the footer.
3) Create your pages (use the view - page break preview) and set the page preaks and row sizes to put the text at the bottom of each page, inserting the text-rows at the bottom of each page

Steve

[Don Wells]

Thanks for the help Don. But I guess that wont help as I want those texts to appear on the footer. And as I want the texts from row 1:12 to appear on the repeating rows.

Adam
    I found the following code which you may be able to tailor to your specific needs.

Please see the next post for the revised code

[Don Wells]

Thanks for the help Don. But I guess that wont help as I want those texts to appear on the footer. And as I want the texts from row 1:12 to appear on the repeating rows.

Adam
    I found the following code which you may be able to tailor to your specific needs

Hi Adam
    I have reworked the code to make it more general in use. The master sheet is copied to a new sheet. The new sheet is manipulated, then printed and deleted. The following rules are laid down for the Master sheet:

• All rows (with the exception of the header and footer rows), must be the same height.
  The footer rows must appear after the rows to be repeated at the top of each page.
  The footer rows must appear on the first page.

     To satisfy your particular needs you will need to revise the code as follows:

• Revise lines 40, 50, & 60 to reflect the final configuration of your master sheet;

Code: Select all

Option Explicit
Sub repeatBotRows()

      Dim HdrRows As Long
      Dim BotRows As Range, BotCount As Long
      Dim FirstPgBk As Long, LasRow As Long
      Dim TotPages As Long, n As Long, m As Long
      Dim TSN As String ' Active Sheet Name
      Dim FFR As Long
      Dim RRPP As Long ' Repeating rows per page

10      Application.ScreenUpdating = False
20      Application.DisplayAlerts = False
30      Application.Calculation = xlCalculationManual
        
        '#####################################
        'Identify the Target Sheet Name      #
40      TSN = "Sheet1"                  '#
        '#####################################
        ' Identify the Header & Footer rows '#
50      HdrRows = 12                    '#
60      Set BotRows = Range("13:17")        '#
        '#####################################
        
70      Worksheets(TSN).Copy After:=Worksheets(TSN)
0      ActiveSheet.Name = "PrintOrig"
90      With ActiveSheet.PageSetup
100         .PrintTitleRows = "$1:$" & HdrRows
110     End With
120     FirstPgBk = ActiveSheet.HPageBreaks(1).Location.Row - 1
130     BotCount = BotRows.Rows.Count
140     RRPP = FirstPgBk - HdrRows - BotCount
150     LasRow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
160     TotPages = Application.Ceiling((LasRow - HdrRows - BotCount) / (FirstPgBk - BotCount - HdrRows), 1)
170     Range(Rows(FirstPgBk + 1), Rows(FirstPgBk + BotCount)).Select
180     Selection.EntireRow.Insert Shift:=xlDown
190     Set BotRows = Range(BotRows.Row & ":" & BotRows.Row + BotRows.Rows.Count - 1)
200     BotRows.Copy Range("A" & FirstPgBk + 1)
210     FFR = FirstPgBk - BotCount + 1
220     Range(BotRows.Row & ":" & BotRows.Row + BotCount - 1).Delete
        
230     n = 2 ' curent page
240     Do
250       Range(Rows((FirstPgBk - HdrRows) * n + HdrRows), Rows((FirstPgBk - HdrRows) * n + HdrRows - BotCount + 1)).Select
260       Selection.EntireRow.Insert Shift:=xlDown
270       Range(FFR & ":" & FirstPgBk).Copy Range("A" & Selection.Row)
280       n = n + 1
290     Loop Until n > TotPages
300   Application.Calculation = xlCalculationAutomatic
310   ActiveSheet.PrintOut
320   ActiveSheet.Delete
330   ActiveSheet.Buttons.Delete
340   Application.DisplayAlerts = True
End Sub

[adam]

Thanks for the help Don. Ill try to adjust the code accordingly.

[Don Wells]

Thanks for the help Don. Ill try to adjust the code accordingly.

I recommend that you also delete line 330. Apologies for not spotting it sooner.

[adam]

Thanks for letting me know what you had skipped out.

Based on your suggestion, I've modified the sheet and placed the code in a standard module. When the macro is run, the printer opens and the sheet gets printed. But since I have no printer connection (to my laptop) I don't know how the sheet gets printed.

If I print preview the sheet, the texts which I want to appear in the footer appears on the first page( as per your suggestion) so I dont know where it stays when the sheets are printed. From print preview I could see it on the first page only.

My question of concern is that how could I preview the sheet so that the yellow cells also get previewed in every page's footer.

Wouldn't it be possible to place the text (in yellow cells) to the bottom of the sheet instead of placing them under the rows to repeat at the top and them place them in every page's footer?

Any help on this would be kindly appreciated.

I've attache the modified version of the sheet with the modified version of the code provided.

Thanks in advance.

[Don Wells]

Thanks for letting me know what you had skipped out.

Based on your suggestion, I've modified the sheet and placed the code in a standard module. When the macro is run, the printer opens and the sheet gets printed. But since I have no printer connection (to my laptop) I don't know how the sheet gets printed.

If I print preview the sheet, the texts which I want to appear in the footer appears on the first page( as per your suggestion) so I dont know where it stays when the sheets are printed. From print preview I could see it on the first page only.

My question of concern is that how could I preview the sheet so that the yellow cells also get previewed in every page's footer.

Wouldn't it be possible to place the text (in yellow cells) to the bottom of the sheet instead of placing them under the rows to repeat at the top and them place them in every page's footer?

Any help on this would be kindly appreciated.

I've attache the modified version of the sheet with the modified version of the code provided.

Thanks in advance.

It will be necessary to add lines 31, 32, & 33 below, to the existing code following line 30; also rem out line 310; and delete line 320.

You will now be able to print preview the "PrintOrig" sheet. Be cautious; This is not the sheet to accept changes to your data, changes must be made on the master sheet.

Code: Select all

31      On Error Resume Next
32      Sheets("PrintOrig").Delete
33      On Error GoTo 0

[Don Wells]

A further thought, add line 315 as shown below:

Code: Select all

315   ActiveSheet.PrintPreview

[adam]

Thanks for the help.

The footer (texts in yellow cells) gets copied to the first and second page break of the sheet only. But it does not get copied to the remaining page breaks of the sheet where the text remain.

What may be the reason for this?

[Don Wells]

Thanks for the help.

The footer (texts in yellow cells) gets copied to the first and second page break of the sheet only. But it does not get copied to the remaining page breaks of the sheet where the text remain.

What may be the reason for this?

I am unable to replicate your problem. Can you forward a workbook with dummy data which exhibits the behaviour you describe?

[adam]

Attached please find the workbook which does show what I've asked.

In the attached workbook you could see that the footer gets replicated in the new sheet only two times instead of getting replicated to all the page break lines with the data rows.

Suppose if the last data row in my sheet is 190 I'm trying to make the footer to get copied from the first page to the page where this row exists.

At present the footer gets copied only to the first two pages.

I hope this clears my question.

[Don Wells]

Try putting some data in Row 190. The macro does not run to the end of formatting, but to the end of data.

[adam]

Thanks for the help.

By the way; why should the footer rows must appear after the rows to be repeated at the top of each page?

Couldn't the code be adjusted in a way so that when the footer text is placed in the end of the last page where it gets copied to the page break of every page in the new sheet when the macro is run?

[Don Wells]

Thanks for the help.

By the way; why should the footer rows must appear after the rows to be repeated at the top of each page?

Couldn't the code be adjusted in a way so that when the footer text is placed in the end of the last page where it gets copied to the page break of every page in the new sheet when the macro is run?

The footer rows are initially placed in the first page to allow determination of the first page break location. Placing it elsewhere will require moving it onto the first page prior to placing it at the bottom of page 1.

[adam]

The footer rows are initially placed in the first page to allow determination of the first page break location. Placing it elsewhere will require moving it onto the first page prior to placing it at the bottom of page 1.

Does this mean the code cannot be adjusted to work if I place the footer on the last page after the last data row?