Reporting in Excel

[Fernando Ronci]

Hi,

Does Excel have any reporting facilites ? I mean, my goal is that a macro,
after processing and crunching data and numbers, creates a report and
presents it to the user on a new pop up window. If needed, the user should
be able to print the report too. No scrolling should be involved as the size
of the report fits on a single screen (regardless of the resolution)
I know I could create the report in a empty range of cells and the select
them, but I want the interface to be more user friendly.

Thank you,

Fernando Ronci
E-mail: (e-mail address removed)

 

[Iain King]

Does Excel have any reporting facilites ? I mean, my goal is that a macro,
after processing and crunching data and numbers, creates a report and
presents it to the user on a new pop up window. If needed, the user should
be able to print the report too. No scrolling should be involved as the size
of the report fits on a single screen (regardless of the resolution)
I know I could create the report in a empty range of cells and the select
them, but I want the interface to be more user friendly.

This is fairly involved. The way I do it (which might not be the best way)
is to create another sheet (call it 'Report'), and lay it out as a report,
with cells in which the report data can be fed into. You can set up print
margins etc. on this sheet so that it is ready to print whenever. Name all
the cells on the sheet which would hold data.
Then you have to write a macro which will copy values from your normal sheet
into the relevant places on the report sheet (it helps if you name the
relevant cells on the main sheet too). for example:

sub DoReport()
with sheets("Report")
.range("Forename").value = sheets("Main").range("Forename").value
.range("Surname").value = sheets("Main").range("Surname").value
.range("Date").value = sheets("Main").range("Date").value
.range("HoursWorked").value =
sheets("Main").range("HoursWorked").value

.PrintOut
end with
end sub

you can bind the macro to a button on your main sheet, or to a custom
toolbar button, or keyprees, whatever you want.

Iain King