Dissect a file into several sheets or files

[MrRJ]

Hi,
I have a spreadsheet that I would like to dissect into smaller reports based
on certain criteria in several columns. What is the best approach to this?
Create a macro, if so, can you give me an idea on howabout to do so?
There are 20+ columns and more than 5000 rows.
I can filter certain things in several different rows. After I am done with
my filters, I want to copy the end product to another sheet or file.

I welcome suggestions on the best approach to get this done.
MrRJ

 

[Joel]

The question is if you really need a macro or can do it manually. If you are
goijng to use the same set of filters over again then it makes sense to use a
macro. If everytime you make a report it is going to be unique then a macro
is not necessary.

If you want a macro I suggest you record a macro while manually performing
the steps. Start the macro recorder by going to menu Tools - Macro Record
New Macro.

Either if you need a macro or don't need a macro apply the filter by
selecting the column You want to filt and then go to menu Data - Filter -
Auto Filter. Repoeat this for each column you want a filter or select every
column. Then in Row 1 select the filter you want to use. Next copy the
visible cells and paste on a new worksheet..

Then stop recording macro by going to menu Tools Macro - Stop Recording.

 

[MrRJ]

Thanks for your help Joel. I will be creating a macro.

 

[Gord Dibben]

See Ron de Bruin's Copy/Paste/Merge examples.

http://www.rondebruin.nl/tips.htm

Maybe specifically this page.

http://www.rondebruin.nl/copy1.htm


Gord Dibben MS Excel MVP