NEED TO REPEAT ROWS AT THE BOTTOM OF EXCEL SPREAD SHEET

[AQ Mahomed]

Hi There

Microsoft Excel cannot repeat rows at the bottom of a spread sheet, I
need assistance in writing a macrow to repeat rows at the bottom of a
spread sheet

Many Thanks in advance
AQ

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Gord Dibben]

AQ

I'm sure Excel can do this.

More details required please.

What do you consider the bottom of the spreadsheet?

What rows do you want to repeat?

Gord Dibben Excel MVP

 

[AQ Mahomed]

Hi

When printing a report i need rows 2 to 7 to be printed at the bottom of
every page.

Many Thanks
AQ


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Gord Dibben]

AQ

Excel has no built-in function to allow "repeating rows at bottom", only at
top.

Print them separately and then print the real data a second time. (Adjust the
margins both times).

Maybe even copy the "footer rows" into Word and print a bunch of forms, then
send those sheets through the printer once again for the real data.

In xl2002 you could create a picture of your rows 2 through 7 and insert that
picture in the footer.)

Gord Dibben Excel MVP

 

[AQ Mahomed]

Hi

I have approx 500 Pages to print is there no way that you can assist me
in writing a macro to print the rows at the bottom of evey page.

Thanks AQ


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Art Farrell]

Hi AQ,

This macro will copy the original sheet to a new sheet and do the work
there. I put statements in to print the sheet and delete it but I turned
these off since you can go to Print Preview to verify that it works as you
want. Let me know if you have questions.

CHORDially,
Art Farrell


Option Explicit
Sub repeatBotRows()

Dim botRows As Range, botCount As Long
Dim firstPgBk As Long, LasRow As Long
Dim totPages As Long, n As Long, m As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set botRows = Range("2:7")
Sheets("Sheet1").Copy after:=Sheets("Sheets1")
ActiveSheet.Name = "printOrig"
With ActiveSheet.PageSetup
.printtitlerows = "$1:$1"
End With
firstPgBk = ActiveSheet.HPageBreaks(1).Location.Row - 1
botCount = botRows.Rows.Count
LasRow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
totPages = Application.Ceiling(LasRow / (firstPgBk - botCount - 1), 1)
Range(Rows(firstPgBk - botCount + 1), Rows(firstPgBk)).Select
Selection.EntireRow.Insert Shift:=xlDown
botRows.Copy Range("A" & firstPgBk - botCount + 1)

n = 2
m = 0
Do
Range(Rows(firstPgBk * n - botCount - m), Rows(firstPgBk * n -
m - 1)).Select
Selection.EntireRow.Insert Shift:=xlDown
botRows.Copy Range("A" & firstPgBk * n - botCount - m)
n = n + 1
m = m + 1
Loop Until n > totPages
Application.Calculation = xlCalculationAutomatic
' ActiveSheet.PrintOut
' ActiveSheet.Delete
ActiveSheet.Buttons.Delete
Application.DisplayAlerts = True
End Sub