mprija
12-13-2006, 07:50 AM
I have a macro which I wrote with help from members of this forum, it worked fine for me, but now It reached its limit. Problem is that pricelists with which I am working are now so big, that if I code whole list of all airport codes in macro, the macro simply do not work, because code is too long.
So I was thinking and got some ideas how best would be that new macro will work. But I am still green in programing so I would like to ask for help with this macro programing.
Bellow it is a list of things I would like macro will do, I have attached 2 files to this post, so it will be easyier to understand form of table with which I need to work. I am always getting it in same form.
1. Macro should first in test.xls file make new worksheets with same name as text in cells of first row - worksheet LIST in macro.xls file (example: AU - EU1, AU - EU2,...)
2. In next step it should copy and paste first row from worksheet FARES (test.xls) to all newly created worksheets in test.xls (example: AU - EU1, AU - EU2,...)
3. Then it should compare 3 letter airport codes from WS LIST in macro.xls file with airport codes in 1st collumn of WS FARES in test.xls file and copy them with apropriate prices to apropriate worksheets.(In example test.xls file: CDG, GTW, AMS and FRA to AU - EU1 and LYS, LHR, MUC to AU - EU2)
Airport codes on FARES WS are in string separated by /.
4. It should put double stars (**) in first empty cell of Row A on newly created worksheets.
------------------------------------------------------
Notes:
- Macro should make as many new worksheets as many first row cells will have names
- If there would be any new 3 letter code which macro would not find in LIST WS it should disply it in text box. (example: XYZ is not in LIST WS but it is in FARES WS, so it should be displayed...)
- TEST.XLS file its only short example of pricelist, I usually have to work with tables with 200 and more rows of data, so it should be possible to insert more WS names and airport codes into LIST WS for macro to process it.
- Macro code would be saved in a module in same file (macro.xls) as LIST WS is located.
For any help I will be grateful.
So I was thinking and got some ideas how best would be that new macro will work. But I am still green in programing so I would like to ask for help with this macro programing.
Bellow it is a list of things I would like macro will do, I have attached 2 files to this post, so it will be easyier to understand form of table with which I need to work. I am always getting it in same form.
1. Macro should first in test.xls file make new worksheets with same name as text in cells of first row - worksheet LIST in macro.xls file (example: AU - EU1, AU - EU2,...)
2. In next step it should copy and paste first row from worksheet FARES (test.xls) to all newly created worksheets in test.xls (example: AU - EU1, AU - EU2,...)
3. Then it should compare 3 letter airport codes from WS LIST in macro.xls file with airport codes in 1st collumn of WS FARES in test.xls file and copy them with apropriate prices to apropriate worksheets.(In example test.xls file: CDG, GTW, AMS and FRA to AU - EU1 and LYS, LHR, MUC to AU - EU2)
Airport codes on FARES WS are in string separated by /.
4. It should put double stars (**) in first empty cell of Row A on newly created worksheets.
------------------------------------------------------
Notes:
- Macro should make as many new worksheets as many first row cells will have names
- If there would be any new 3 letter code which macro would not find in LIST WS it should disply it in text box. (example: XYZ is not in LIST WS but it is in FARES WS, so it should be displayed...)
- TEST.XLS file its only short example of pricelist, I usually have to work with tables with 200 and more rows of data, so it should be possible to insert more WS names and airport codes into LIST WS for macro to process it.
- Macro code would be saved in a module in same file (macro.xls) as LIST WS is located.
For any help I will be grateful.