Auto filter macro

[Raju Radhakrishnan]

Hi,

I have attached an excel file...I want a macro which would give me option to select the file from the directory and then auto filter as below:

1) Filter Task status open and copy everything to sheet two.
2) Then remove the filter and filter again to container number and select blanks and copy those data again to sheet two with the earlier data.
3) Then i want it saved in the same directory where it was taken initially.

NOTE: - if possible i need this macro to run in multiple files at a time as we get many files

Thanks any help will be really really really really appreciated

[arlu1201]

Question on this part-

2) Then remove the filter and filter again to container number and select blanks and copy those data again to sheet two with the earlier data.

In your sample data, the 4th line shows as "Open" and it has a blank for container number. So as per step 1, it will already be copied. Then as per step 2, this same line will be copied again. Wont this be duplication?

When you say the data should be copied along with the earlier data, should it be copied below or to the right side?

Also, you said you want to run this macro on multiple files, so should the macro give the user a popup to open the file?

[Raju Radhakrishnan]

1) Yes it will be duplicated but both has to be copied as while working on it we filter on the shipment number.
2) Data should be copied from the excel sheet to a newly created excel sheet.
3) Yes macro should give the user a popup to open the file and if possible to select multiple files at a time to reduce time wastage

Thanks for replying

[arlu1201]

Data should be copied from the excel sheet to a newly created excel sheet.

Do you mean a new excel workbook or just a new sheet created in the same file?

Yes macro should give the user a popup to open the file and if possible to select multiple files at a time to reduce time wastage

You want the macro to open one file, then run the macro on it, then close it and open the next file right? Or do you want the user to run the macro each time they want to work on a particular file?

Opening multiple files at one time will not help if you want the macro to work on all. It will have to open one file, run the macro, close the file, open the next file, etc.

What do you suggest?

[Raju Radhakrishnan]

It should automatically open and do the filter thing and close...and again open...but i want the macro to give an option to the user to select on which all files does the macro will do this

[Raju Radhakrishnan]

Any updates bro ???....Thanks..

[arlu1201]

Do you want the macro to give the user a popup for each file that has to be opened?

If there are too many such files, i would suggest, you keep all of them in one folder. The macro can then open each one, execute the steps, close it without having to ask the user to open each one.

[Raju Radhakrishnan]

Yes i need that popup! max to max there will be 35 files inside..... :-) and if at all more than that ....every file will be in one folder itself!

[arlu1201]

So you will need to choose between one of the 2 options which are - 1. Open through a popup for all files
2. Open directly from folder without any popup.

[Raju Radhakrishnan]

First option is better...For ex if i have 35 files..I should get to choose which all files do i want...ok?

[Raju Radhakrishnan]

Any updates bro?