Sheet and workbook code: All sheets should have row 1 to 5 of Sheet1to be repeated at top

[Subodh]

I want to make code to make all the sheets have same ROWS TO REPEAT AT
TOP on all the sheets of a workbook. ie. all the sheets in a work book
should have row1 to row5 of sheet1 to repeated at the top.
But, they cannot be made?
The Sheets to repeat on top is only for sheet
I want to make the sheet code for the workbook.

 

[JLatham]

Your best solution is to copy rows 1 through 5 from Sheet1 into all other
sheets and use Page Setup to repeat rows 1:5 on each sheet when it is printed.

If the entries in rows 1:5 of Sheet1 change often and you need to have the
same entries on all other sheets, then set up the entries on the other sheets
to link back to the appropriate cell on Sheet1. For example, in cell A1 of
each of the other sheets, you could put the formula:
=Sheet1!A1
You can then easily 'fill' that formula over and down the sheet for as many
columns and down to row 5 so that whatever is on Sheet1 in rows 1:5 always
appears on the other sheets.

 

[Gord Dibben]

Sub titles()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.WorkSheets
If ws.Type = xlWorksheet Then
ws.PageSetup.PrintTitleRows = "$1:$5"
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Ignore this.

Misread the question.


Gord

Sub titles()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.WorkSheets
If ws.Type = xlWorksheet Then
ws.PageSetup.PrintTitleRows = "$1:$5"
End If
Next
End Sub


Gord Dibben MS Excel MVP