Duplicate and serialize rows

[Slovenian]

Dear hive mind,

I work in library-digitization, am no stranger to renaming software, xml, Excel and macros, but I'm not an expert, I usually adjust the code, unfortunately am not on able to code from scratch.

I am humbly asking for help with my task.

I have spreadsheet with two columns, one contains the first page of a book with old wrong file name, next one with correct name. I already have a way to rename actual files based on spreadsheet.

The challenge is that I only have the file names of first page of the book in Excel rows, but I have to rename all the scans from the book, and it can have random number of pages ranging from 1 to 100.

I'm looking for a formula or macro that does the following:
- It copies every row 100 times (both columns)
- It serializes the all 100 duplicate rows - changes last 3 digits in the name (both columns)
Example:

Before:
id-AAAAA_scn-001.jp2 id-WWWWW_scn-001.jp2 First book, first page

id-BBBBB_scn-001.jp2 id-XXXXXX_scn-001.jp2 Second book, first page

After:
id-AAAAA_scn-001.jp2 id-WWWWW_scn-001.jp2
id-AAAAA_scn-002.jp2 id-WWWWW_scn-002.jp2
...
id-AAAAA_scn-100.jp2 id-WWWWW_scn-100.jp2

id-BBBBB_scn-001.jp2 id-XXXXXX_scn-001.jp2
id-BBBBB_scn-002.jp2 id-XXXXXX_scn-002.jp2
...
id-BBBBB_scn-100.jp2 id-XXXXXX_scn-100.jp2

If the .jp2 represents a hurdle I can remove it and add it back, I'm just hoping someone can help with duplicating and serializing the spreadsheet.

Once I have a list I can rename files that exist and skip ones that don't exist, so it's no problem if a book has 4 or 100 pages and I've checked and most pages is 80.

Hope someone more skilled can help and likes the challenge. Hopefully I've attached a sample spreadsheet.

Thank you in advance I'll would be very grateful for any help since this is only one part of a bigger puzzle I'm trying to solve. Best regards

[jindon]

vba

Please Login or Register  to view this content.

[Slovenian]

Jindon thank you so much for your help! Your code works even better than I dared to hope.

If there is any way I can show my gratitude other than adding reputation let me know. Have a great day, you sure made mine better.

[jindon]

You are welcome and thanks for the rep.

Thanks for the kind offer, but "Thank you" is the mightly word to make us happy and good enough...