My workbook contains membership records for a club. The main worksheet is a
register (database) containing all the relevant details of each membership,
including whether the current subscription has been paid.

When I need to print a list of, say, all the members who have not paid yet
OR all the members who do not have email addresses, I do it the long way - by
sorting the register worksheet on the appropriate column and then copying the
required records to to a new worksheet. I then modify the worksheet to
display what is needed and print it. Then I have to sort the register back
into main key order.

Is there any shorter method of doing this, perhaps by inserting an array
formula into a new worksheet? I could probably write a VBA routine to do
this, but as I am still a beginner with VBA I would prefer to find an
existing solution.

"Hershmab" wrote:

My workbook contains membership records for a club. The main worksheet is a
register (database) containing all the relevant details of each membership,
including whether the current subscription has been paid.

When I need to print a list of, say, all the members who have not paid yet
OR all the members who do not have email addresses, I do it the long way - by
sorting the register worksheet on the appropriate column and then copying the
required records to to a new worksheet. I then modify the worksheet to
display what is needed and print it. Then I have to sort the register back
into main key order.

Is there any shorter method of doing this, perhaps by inserting an array
formula into a new worksheet? I could probably write a VBA routine to do
this, but as I am still a beginner with VBA I would prefer to find an
existing solution.

More information:
What I am looking for is a sort of COPYIF function that will return an
array. The function would:
- search through one column of the database
- wherever it found a cell meeting the specified condition copy the
corresponding row (or specified call-range) into the next available row of a
specified worksheet.

So instead of sorting on the column containing the condition it would search
through the database in situ and make a compact copy of all the matching rows
in a new worksheet.

Hi,

Choose Data, Filter, AutoFilter. Open the autofilter dropdown on any column
you want to look at and choose (blanks). You can print the results and the
filters don't show. To return to seeing everything just open the dropdown
and choose "all"
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Hershmab" wrote:

My workbook contains membership records for a club. The main worksheet is a
register (database) containing all the relevant details of each membership,
including whether the current subscription has been paid.

When I need to print a list of, say, all the members who have not paid yet
OR all the members who do not have email addresses, I do it the long way - by
sorting the register worksheet on the appropriate column and then copying the
required records to to a new worksheet. I then modify the worksheet to
display what is needed and print it. Then I have to sort the register back
into main key order.

Is there any shorter method of doing this, perhaps by inserting an array
formula into a new worksheet? I could probably write a VBA routine to do
this, but as I am still a beginner with VBA I would prefer to find an
existing solution.

"Shane Devenshire" wrote:

Hi,

Choose Data, Filter, AutoFilter. Open the autofilter dropdown on any column
you want to look at and choose (blanks). You can print the results and the
filters don't show. To return to seeing everything just open the dropdown
and choose "all"
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Great, it works almost exactly as I want.

(For some reason I never think of using Filter. In fact I have used it
unknowingly - in running Word Mailmerges from my Excel database!)

The thing about getting this sort of help is that it always raises more
demands. In this case I am producing exception reports that I need to
distinguish from the normal complete printout of the database. So when
Autofilter is in effect:

(1) Can the filter conditions be automatically shown in the print header?

(2) Can certain groups of columns be omitted without having to hide each one
individually?