creating repeating headers in a worksheet

[larinoz]

I need to create a worksheet with a headline that repeats over several rows.

The items in this worksheet need to then be sorted by date and divided into
week beginning sections

ex. row 1 headers Company Product amt.....
Row 2 Week beginning
Row 3 thru 10 then has info for this week

Row 11 would repeat headers
row 12 Week beginning
row 4 thru ? has info

This will then repeat for each week throughout a full year

How do I create these permanent headers that can have an arbitrary # of rows
under each one?

Any help would be greatly appreciated. If I could figure out the template
then it would save me tons of typing/copy and pasting and then I would like
to figure out How I could then enter data and have it sort itself into the
correct week.

 

[RajKohli]

Can easily be done but the only problem is that you wanted this on irregular
positions.

The Heading Rows can easily be repeat on each page. Mind me that Each Page
not at irregular rows. First one is to write each weak data on seprate pages
and heading only at first page then:

1. File - Page Setup - Sheet
2. Rows to Repeat at Top
3. The select the desire rows you want to repeat at top of each page.

If you do not want to leave blank rows between two weeks data you can you
Page Breaks:

When using the Print button on the Standard toolbar, the document printed on
two pieces of paper. Whenever a document is longer than the default 11 inches
height, the printing process will segment it in sections to fit the legal
height. If you don’t like the way the printer sets the page limit, you can
set your own and tell the printer where you want each page to start. To do
that, you use the Page Break feature.

You don’t have to set a page limit the traditional way which consists of
using the end of a real end of a page. Microsoft Excel allows you to start a
new page anywhere on the worksheet. To set your own page end, first click the
row that will start the subsequent page. Then on the main menu, you would
click Insert -> Page Break. You should notice a line that points to the page
break. The document will be divided in at least 4 sections and each section
represents its own page.

After you have worked with the page break, or if you don’t need the page
break anymore, you can remove it. To remove a page break, on the main menu,
you can click Insert -> Remove Page Break.

But don't forget that this method will too print the data on different
pages. Both the above method will make the Data Sorting easier.

Last but not least, you can Record a Macro for your headings and assign a
shortcut key. Whenever you need heading just press the Shortcut key. I will
not suggest this one because the Data Sorting then may not be possible or may
have much difficulties.

Hope this help, let us know!

 

[David McRitchie]

weekly_chronology.xls worksheet for 2007
as requested in http://groups.google.com/groups?threadm=800C66E1-0D52-4537-9BC1-DBDFAC73CEEB@microsoft.com


Fill in the information you want to copy down into
rows 1 through 10. With date in cell A2 (as you would show Sun Dec 31, 2006)
bold cells, and format cells in rows 1-10 as wanted

select rows 1-10 and paste to A11
change the date in cell to =A2+7
select row 11, insert (menu), page break
select rows 11:20
Ctrl+C
enter 21:530 into the name box to the left of the address bar
hit "Enter"
Right click to bring up context menu

Inserting page breaks did not work with copies, and you probably
want 5 weeks per page to save paper, looks like you can fit 5 sets
so use the following (or change 50 to 40 for 4 sets per page)

if macros are new to you
see http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

Sub PageBreak_step50()
Dim CellRange As Range
Dim TestCell As Range
Dim i As Long
ActiveSheet.ResetAllPageBreaks
For i = 50 + 1 To 530 Step 50
ActiveSheet.Rows(i).PageBreak = xlPageBreakManual
Next i
End Sub


if you made a mistake:
to start over enter 21:600 into the name box
insert (menu), clear page breaks
edit, clear, all

Create an Event macro, to get you to the week
Event macros are not the same and are described at
http://www.mvps.org/dmcritchie/excel/event.htm

right-click on the sheet tab
View Code, and insert the following:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim calc As Long, caddr As Range
calc = (Now() - Range("A2").Value - 1) / 7 * 10
calc = Int(Int(calc) / 10) * 10 + 1
Application.Goto Reference:=Range("A" & calc), scroll:=True
Range("A" & calc).Offset(2, 0).Select
End Sub

 

[David McRitchie]

directions was missing cell address to be changed:
select rows 1-10 and paste to A11
change the date to a formula in cell _A12_ to =A2+7