Module to apply to all open worksheets

Hi,

I have a module, which lists, by date, the number of instances of certain words as defined at the top of the column.
Anyway, this works fine - it operates on all sheets of a certain name type:

Please Login or Register  to view this content.

Which is what I want, but I want it to also operate on other sheets outside of the workbook eg all open sheets which fit the name criteria regardless of workbook - or all sheets within a given directory which match the criteria

currently its:

Please Login or Register  to view this content.

but I'm not sure what to change it to - thinking it must be relatively simple..?

the module code is this:

Please Login or Register  to view this content.

Actually, I think what I need is the following:

Some way to copy data from multiple sheets in multiple workbooks to one main sheet on another workbook...

that way I could pull all the data from the other worksheets in the other workbooks into a master sheet in my current workbook and run the module as normal...

Thanks for your responses

Ideally I would have it working on all workbooks in a given directory - this is safer.

but it should definitely work on ALL workbooks in that directory, as that will be exclusively for the "raw data" books with data sheets in them.

There would however be a specific range within the sheets that I would wish to copy - not the sheet - so the range would be like

A3:F250



Another thing that would be useful is if it only copied rows with data in them - but I don't want to ask for too much!!

Thanks DonkeyOte.

Yeah, the reason I want to seperate what is currently one workbook into multiple books is the following:

The workbook is updated several times every day, sometimes by a few different people per day. The workbook is already pretty big and is only going to grow. I don't want folk to have to open a massive workbook every day.

By dividing the workbook into one master sheet with the reporting charts on it and multiple - "quarterly data books" - the frequently updated data books are smaller and more manageable.

When someone wants to view the stats for the period covered by all the data sheets, (which is infrequently,) it takes a bit more effort eg running a macro inthe master book to draw all the relevant data in from all the books.

oo er

that sounds scary.

If that is to be a solution, it would have to be one built by a different department. And to be honest, that's not going to happen.

All I really want now is the best solution for having this big amount of data in excel workbooks, and being able to draw statistics from it in the way I've described.

It may not be the slickest, but with the resources available, this is the best I can hope for..

So ultimately it seems to be a choice of

1. Creating a macro which when run pulls a specific range of data from sheets with a certain name type within open workbooks / all workbooks within a given directory

2. Modifying the existing macro (as detailed in the first post of this thread) so that it operates on all open workbooks / all workbooks within a given directory.

I can't figure out how to do either, so any help here would be much appreciated.

Hello,

Just wanted to raise this one up again to see if anybody who hadn't seen it first time would have any ideas about potential solutions.

In a nutshell - I have a working module which creates a table in one sheet by referencing data in various other sheets. These are all currently in the same workbook, but I want to divide the worksheets into a number of different workbooks and have the table reference "all open workbooks" or "all the workbooks in a given directory"

Is there a way to do this?

(the existing module is at the start of this thread)

Thanks