Thanks. This will help a lot. I'm assuming Column A is represented the
numeral 1 in the third line?

The formulas are percentages placed adjacent to the data. For example,
Column C has the data, Column D is the percentage.

My example would be H, I then J, K, then L, M

The formula is the same in each (with the only difference being the columns
in the range).

=(COUNTIF(H2:H68, "Yes"))/(COUNT(C2:C68))

This is from the first group of my report. (For what its worth, I've
noticed when I copy and past the end of the range updates correctly each
time, but the beginning seldom does and is usually only 4 or 5 away from the
end. )

Again, many thanks for your help.

Charles

"Gord Dibben" wrote:

This macro will take care of the pagebreaks.

Not enough info to look after formulas but prolly can be done.

Sub InsertBreak_At_Change()
Dim I As Long
For I = Columns(1).Rows.Count To 1 Step -1
If Selection(I).Row = 1 Then Exit Sub
If Selection(I) < Selection(I - 1) And Not IsEmpty _
(Selection(I - 1)) Then
With Selection(I)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub

Assumes the names are in column A.

Select the column range then run the macro.


Gord Dibben MS Excel MVP


On Mon, 2 Nov 2009 07:02:02 -0800, randlesc
wrote:

Sorry, but I've been told by the administrator of the hospital that it must
be in Excel. I have tried to argue the advantages of it being in Access but
administration remains firm that it be an Excel report.

So, can anyone help?

BTW, I didn't mention but perhaps I didn't need to, the number of employees
changes weekly, both up and down.

"Geoff_L" wrote:

If you keep the information in Access you could create a report that was a
lot easier to format than in Excel. You can create all the subtotals etc
that you need and have full control over how they appear. You can also force
a new page for each group, if the whole group won't fit on the remainder of
the page. If you are looking for presentation and not just raw facts and
figures, Access is the way to go - unless you have a lot of statistical
calculations to do. Even then it would be very simple to reimport it into
Access once you have done the statistical acrobats in Excel.

"randlesc" wrote:

I have a big report at work due every Tuesday that requires me to take
between 4,000 and 5,000 rows of information from Access and export them to
Excel.

My problem is that since this is an HR report, it needs to be divided by
department of which there are about 170 them. I need a page break after each
one.

Also, I have formulas that calculate the percentage of employees in each
department who have completed certain training.

What is the best way to do this? The powers that be do not like the look of
the page if I use the subtotal feature.

Currently, after I export the report to Excel, I go through and manually add
page breaks after each department and at the same time add the formulas. It
takes about four hours to do this manually.

Help?

.