Copy from one list to multiple sheets with different values for each sheet + renaming

Hello.

I have a question. I have 501 sheets in one excel file. Every sheet represent an article. Last sheet #501 have a list of article number with different weeks when they will be sold. I have to put an artticle code at every single sheet from the list and rename this sheet by the name opf article. SO for instance I have such sheet #501 (last one)
article number / week
200005 08
654654 09
123123 08
111222 04
555888 08

So I want to do a week 8. So as result I have to have a name of the tab "200005" instead "tab1" and to have in a cell A6 the numver 20005
the tab2 will be 123123 and will have 123123 in A6. And so on.

Can you help me to solve the problem?

P.S. The renaming feature I did from here by VBA code (because the list of articles is on another separate sheet), but it is a bit bugged and have mistake message (but nevertheless does its job).

Thank you in advance!

The file is corrupted a bit..."Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Removed Records: Sorting from /xl/worksheets/sheet1.xml part". But I have opened it and tried it, thank you!

Thank you for trying to help me, but I can just add a VBA code to the macros because I cannot change an sample docs with my own form. So the only thing I can do is to create a macros for this. That is why I will be very greateful for a VBA code.

I upload a doc to show how does it look. first 3 sheets are not renamed and empty, so this is what do I have. 4th sheet is this the last sheet with list of srticles from where I have to take all codes of article. ANd next 3 sheets are the result (sorted by week 5) I expecting to have. Hope it will help....week 5.xlsx

Maybe:

Please Login or Register  to view this content.

That is a very good thing, but there are some problems. I do not need to create new tabs after the last one.

The best variant would be with messages "choose the list of the codes of article" and "choose the cell where to put the code of article". This question system was realized very good here with row comparation. And after the macros has to edit all tabs from the first one untill the end of the selected list after the 1`question with renaming of them accordingly with their article number. Thank you for help!