Text in every page footer

[Don Wells]

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

The code has been reworked to allow the initial position of the footer to be placed anywhere after the header..

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
        
40      On Error Resume Next
50      Sheets("PrintOrig").Delete
60      On Error GoTo 0
        
        '#####################################
        'Identify the Target Sheet Name      #
70      TSN = "StockInformation"
        '#####################################
        ' Identify the Header & Footer rows '#
80      HdrRows = 12
90      Set BotRows = Range("160:164")
        '#####################################
        
100     Worksheets(TSN).Copy After:=Worksheets(TSN)
110     ActiveSheet.Name = "PrintOrig"
120     With ActiveSheet.PageSetup
130         .PrintTitleRows = "$1:$" & HdrRows
140         .PrintArea = ""
150     End With
        
160     BotCount = BotRows.Rows.Count
170     Range(Rows(HdrRows + 1), Rows(HdrRows + BotCount)).Select
180     Selection.EntireRow.Insert Shift:=xlDown
190     Set BotRows = Range(Rows(BotRows.Row + BotCount), Rows(BotRows.Row + 2 * BotCount - 1))
200     BotRows.Copy Range("a" & HdrRows + 1)
210     Range(Rows(BotRows.Row), Rows(BotRows.Row + BotCount - 1)).Delete
220     Set BotRows = Range(Rows(HdrRows + 1), Rows(HdrRows + BotCount))
230     FirstPgBk = ActiveSheet.HPageBreaks(1).Location.Row - 1
240     RRPP = FirstPgBk - HdrRows - BotCount
250     LasRow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
260     TotPages = Application.Ceiling((LasRow - HdrRows - BotCount) / (FirstPgBk - BotCount - HdrRows), 1)
270     Range(Rows(FirstPgBk + 1), Rows(FirstPgBk + BotCount)).Select
280     Selection.EntireRow.Insert Shift:=xlDown
290     Set BotRows = Range(BotRows.Row & ":" & BotRows.Row + BotRows.Rows.Count - 1)
300     BotRows.Copy Range("A" & FirstPgBk + 1)
310     FFR = FirstPgBk - BotCount + 1
320     Range(BotRows.Row & ":" & BotRows.Row + BotCount - 1).Delete
        
330     n = 2 ' curent page
340     Do
350       Range(Rows((FirstPgBk - HdrRows) * n + HdrRows), Rows((FirstPgBk - HdrRows) * n + HdrRows - BotCount + 1)).Select
360       Selection.EntireRow.Insert Shift:=xlDown
370       Range(FFR & ":" & FirstPgBk).Copy Range("A" & Selection.Row)
380       n = n + 1
390     Loop Until n > TotPages
400     Application.Calculation = xlCalculationAutomatic
'410     ActiveSheet.PrintOut
420     ActiveSheet.PrintPreview
430     Application.DisplayAlerts = True
End Sub

[adam]

Thanks for the help Dons. And sorry for my late response.

[adam]

By the way, if the footer text is placed above the headers the code wouldn't work. right?

[adam]

In the attached workbook the code creates a new page where the text in yellow gets copied to the footer of every page. But it does not get copied to the last page; meaning the 13th page.

Why is this?

How could the code be made to work same as above so that when the text in yellow is placed on top of the headers starting from row 11(where the texts in blue) shows.

Let’s say for example when the macro is run the rows 11:15 gets hidden and the text in row 11:15 gets reputed on footer of every page when the sheet is printed.

Any help on this would be kindly appreciated.

[HansV]

The print area ends at the row with Chai in it. You have to extend the print area after adding the bottom rows. To do so, insert the following line below "Loop Until n > TotPages":

Code: Select all

    ActiveSheet.PageSetup.PrintArea = "$D$4:$V$" & (Selection.Row + BotCount - 1)

[adam]

Thanks for the help Hans. Instead of putting the footer text from rows from 19 to 23. How could the footer texts be made to appear on every page's footer when the footer text is placed from 11 to 15 instead of 19 to 23?

[HansV]

Let's see if Don replies - he wrote the code.

[adam]

Ok. Thanks for the help.

[HansV]

Actually, you should be able to figure it out yourself by reading the code. It mentions explicitly where the footer rows are specified.

[adam]

The code has been reworked to allow the initial position of the footer to be placed anywhere after the header..

With this will the code work if the footer rows are place above the header?

[HansV]

Do you REALLY need the footer to be above the header? That seems rather strange...

[adam]

Yeah. I wish it to be above the header. I know it seems strange. But I would be happy if I'm told how to do what I'm requesting.

[HansV]

Please attach a sample workbook. The one you attached higher up in this thread looks strange if you change the rows.

[adam]

Here is a slightly modified version. I have modified the code.

[Don Wells]

Hi Adam
    Some rules regarding the NewOrder sheet:

• The header must be continuous;

• The footer must be continuous;

• All rows other than header or footer must be of the same height;

    The sample which you provided leaves me with the following understanding:

• The intended footer is located at rows 11:15

• The intended header is located at rows 1:10 and 16:18

    The code will not satisfy these last criteria. If you can decide where you want to locate the footer, I will rework the code to accommodate the layout. I suggest:

• Footer    Rows 1:x

• Header    Rows (x+1):y

    The attached file has had the footer repositioned to conform to the current requirements, and the code modified accordingly at lines 50 & 60
    If you will advise where you would like to go from here, I will update the code to satisfy your final layout.

[adam]

Thanks for the reply Don.

Instead of keeping the footer text from rows 20 to 24. I need them to be placed in rows 149:153.
And do the same as previous,when the macro is run.

Lets say for example, the workbook has another sheet named “homepage” when the user runs a macro from the sheet homepage the sheet “NewOrder will open where it will have the 16:148 hidden.

When the user opens the sheet and types the date in cell M152, the hidden row will get unhidden. And when the user clicks the macro on the sheet “NewOrder” the sheet PrintOrigin gets opened where the footer gets on every page.

I hope I have made my question clear.

[Don Wells]

Thanks for the reply Don.

When the user opens the sheet and types the date in cell M152, the hidden row will get unhidden. And when the user clicks the macro on the sheet “NewOrder” the sheet PrintOrigin gets opened where the footer gets on every page.

Hi Adam
    Try the attached.

[adam]

When I write the date in cell M152 the hidden rows does not get unhidden. What am I doing wrong in here?

[Don Wells]

When I write the date in cell M152 the hidden rows does not get unhidden. What am I doing wrong in here?

You may be entering the date in a non-date format. Try CTRL + semicolon, which will enter the current date.

[adam]

I've tried your option. The format of the cell M152 is even set to date format. But still the hidden rows does not get unhidden.