My company will put up a new file daily on the shared folder and the filename
changes according to the date e.g. Shortage_080309.xls. I have to download it
everyday and work on it.

In this file, there are 3 worksheets. First worksheet has all the raw data,
it's the worksheet I need to work on. It's named "Shortage 080309", and the
name of the worksheet also changes according to current date. I don't use
the other 2 worksheets. They are named "Def" and "Sheet3" respectively.

What I am trying to do is to create a macro that can help me to,
1. insert a new worksheet in the same workbook
2. rename the new worksheet to "My Shortage <mmddyy" eg. "My Shortage
080309",
3. copy all data from first/main worksheet "Shortage <mmddyy" eg. "Shortage
080309" and paste it on the newly created worksheet.
4. on the new worksheet, filter the data on column D
5. sort by column D and that's it.

The data on column D (header is named "PRD") is 4 digit Prod number e.g.
0417,0604. Not all Prod numbers will appear in the file everyday. I have a
list of specific numbers (about 10 Prod number out of 1000+) that I want to
filter.

I can create the macro fairly easy in Excel 2007 to select the Prod number I
want, but the custom filter in Excel 2003 only allow me select 2 filter
requirements. I need 10.

Another thing is, the macro will be saved in the Excel file itself. How do
I make it "global", so that I am able to use it on another file?

All tips/advice/guidance are appreciated.

regards,
choo