Automatic page breaks and formulas

[randlesc]

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?

 

[Geoff_L]

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]

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.

 

[Gord Dibben]

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

 

[randlesc]

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

 

[randlesc]

Sorry, one more thing.

Just ran the macro and it said it can't execute in Break Mode. I'm not sure
what this means, I assumed it was the view, so I've changed the view several
times but no luck.

Suggestions.

 

[Gord Dibben]

Don't know why you got into break mode but open VBE and Run>Reset.


Gord

 

[Gord Dibben]

In which columns and cells would you want the formulas?

I am not clear about H, I then J, K

Maybe you would want formulas inserted into a row below each grouping?

Instead of a pagebreak?


Gord

 

[randlesc]

Thanks once again.

H has the data, the formula goes into I
J has the data, the formula goes into K

There are easier ways to do this, I know but I have no choice. It must be
in the set up administration designed--don't get me started on how I feel
about this decision.

Thanks.

 

[Gord Dibben]

After posting my last message I started thinking that perhaps a Pivot Table
may be the way to go rather than macros.

If you wish to have a go at that, see Debra Dalgleish's site for mucho help.

http://www.contextures.on.ca/tiptech.html

Look under "P" for Pivot Tables.

I still can't quite reconcile your posted COUNTIF/COUNT formula with your
description.

Does column C remain a constant divisor for H, J and K?

Can you send me the workbook?

email to gorddibbATshawDOTca Change the obvious.


Gord