PDA

View Full Version : Filtering and copying



Simmo-81
07-20-2009, 09:07 AM
Hi all

Im wanting some advise on the best way to resolve the following situation.

I have roughly 120 excel workbooks contained in a folder.
Each workbook has a specific worksheet with data that i need to copy.
The sheet name is called "RDATA".

The information i need to copy depends on the values in column "G".
There is 12 possible values in column "G". all values are dates. e.g Jan, Feb, Mar etc.

The information needs to be filtered and copied to the active workbook and to 1 of 12 existing worksheets. coincidently named Jan, Feb, Mar etc.

I have used and modified the code by ron de burin which allows me to filter 1 result copy to a worksheet then close and open another workbook.

Due to needing to filter and copy 12 times before closing the worbook i cant use or modify that code to suit my needs.

is what i require possible? or am i asking too much.

stanleydgrom
07-20-2009, 07:45 PM
Simmo-81,

Please post your "active workbook" and at least two of the workbooks that you want to copy data from - scroll down and see "Manage Attachments".

What is the full path to the folder/directory to where the workbooks you want to copy data from?

And, please include in your "active workbook" the "code by ron de burin".


Have a great day,
Stan

Simon Lloyd
07-20-2009, 11:05 PM
Simmo, you need only post a sample workbook fo the same design and structure as your Master workbook, as Stanley points out please include any code.

You say you want to copy filtered data but you didn't say whether you only want the data from the column you filtered or whether you want the entire rows of filtered data?

Simmo-81
07-26-2009, 06:26 AM
Hi All

Sorry for the late response.

I have attached the source workbook to this post. It contains the macro i use to filter and copy the data.

There is only sample data and sheets so nohing of great intrest.

My problem is still the same hough, i would like to filter the open workbook up to 12 times before opening the next workbook.

My comments etc are not the best so please bare with it.

Thanks for the help

Simmo-81
07-26-2009, 06:30 AM
Here is 1 sample workbook that is opened by the macro.

The data that is contained is only sample random data. The structure is similar in that there is multiple sheets contained in the file and the daa sheet values and headings are the same.

Basically all the workbooks have the same layout, they are all named differently and contain different data.

Thanks

stanleydgrom
07-26-2009, 12:18 PM
Simmo-81,

Could you post another sample data file, like file "5A3.xls", that contains 20 rows for April, May and June.

Do your data files only contain information for 3 months?


Have a great day,
Stan

Simmo-81
07-26-2009, 01:04 PM
Simmo-81,

Could you post another sample data file, like file "5A3.xls", that contains 20 rows for April, May and June.

Do your data files only contain information for 3 months?


Have a great day,
Stan

Hi Stan

Ill post the data file when i get on my home computer.

To answer the question though, the files are ongoing. By this i mean data is added periodically throughout the tax year. (April-09 - March-10)

Upto date there is information for 4 months and around August 1st there will be new data added, then 1st September etc.

Simmo-81
07-27-2009, 12:29 PM
Hi Stan

Here is another sample workbook, its basically the same layout just the months in column D have changed.

Thanks for any help

stanleydgrom
07-27-2009, 02:11 PM
Simmo-81,


I have roughly 120 excel workbooks contained in a folder.
Each workbook has a specific worksheet with data that i need to copy.
The sheet name is called "RDATA".

Your latest posted workbook "RWB.xls" does not contain a sheet named "RDATA".

There are three sheets: Sheet1, Sheet2, and, BPPC Detail.

Are we working with sheets "RDATA" in all the workbooks, or "BPPC Detail"?


Have a great day,
Stan