PDA

View Full Version : Repeat Bottom Rows on every printed page of a worksheet



bw5727
01-21-2019, 12:28 PM
Hi there,

I am trying to find a solution to have the same bottom 2 rows of my worksheet print on every page printed for that worksheet.

I've done some research on the options to have a solution similar to print titles and can't find one that works.

The standard footer does not provide enough flexibility with styling, an image isn't responsive to when the columns widths are changed, and I can't locate VBA code that makes this work. Here's what I tried (maybe I input/executed it wrong though):



Sub MyFooter()

Dim StrFtr As String, Rng As Range, Sh As Worksheet, c As Range
Set Sh = Worksheets("Sheet5")
Set Rng = Sh.Range("A55:J55")

For Each c In Rng
StrFtr = StrFtr & c & " "
Next c

ActiveSheet.PageSetup.LeftFooter = StrFtr
End Sub


The rows I want reflected at the bottom of each page simply have a color fill and image that spans columns A-J.

Any help you can provide would be outstanding!

jolivanes
01-22-2019, 11:26 PM
Re: The rows I want reflected at the bottom of each page simply have a color fill and image that spans columns A-J.
These are the last two rows of your total area to be printed, right?
If you would have a print area from A1:I234 then rows 233 and 234 need to be printed on each sheet as the last two rows, right?
Like
Page 1 = A1:I48 and A233:I234
Page 2 = A49:I96 and A233:I234
Page 3 = A97:II144 and A233:I234
and so on
Are there repeating rows at the top? If so, which ones.

jolivanes
01-29-2019, 07:34 PM
Waited for a reply but none came.
How far do you get with this?
Change all references where and if required.

Sub Maybe()
Dim sh2 As Worksheet, lr As Long, a As Long, i As Long, j As Long
Set sh2 = Sheets("Sheet2")
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
sh2.PageSetup.PrintArea = sh2.Range("A1:I" & sh2.UsedRange.Rows.Count).Address
a = sh2.HPageBreaks(1).Location.Row - 3
j = 1
Application.ScreenUpdating = False
sh2.Range(Cells(1, 1), sh2.Cells(lr - 2, 1)).EntireRow.Hidden = True
For i = 1 To WorksheetFunction.RoundUp(lr / a, 0) - 1
sh2.Range(Cells(j, 1), sh2.Cells(i * a, 1)).EntireRow.Hidden = False
ActiveSheet.PrintPreview '<---- Change to PrintOut when you're happy with the result
sh2.Range(Cells(j, 1), sh2.Cells(i * a, 1)).EntireRow.Hidden = True
j = j + a
Next i
sh2.Range(Cells(j, 1), sh2.Cells(lr - 2, 1)).EntireRow.Hidden = False
ActiveSheet.PrintPreview '<---- Change to PrintOut when previous is changed
sh2.UsedRange.EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub