Rename Sheets by using Dynamic Loop for the rows

Hello everyone,

I got the following problem. In column C I got some data like this:
Number of .csv
01
02
03
04

Number of .csv
05
06
07
08

Notice that there is an empty cell in between.The data starts at C12 up to C21. The data is in Sheet2.There are also 8 more sheets( Sheet3 to Sheet10). I want to rename each sheet, starting from Sheet3 according to each cell. For example the Sheet3 to be renamed to 01, Sheet4 to 02.

What I can do is something like this:

Please Login or Register  to view this content.

And repeat this code for every block of data I got by changing everytime the i and the a. But this method is not so optimized because there are cases that the number of rows for each block is not the same and I have to change everytime the i counter. Is there any way to do 1 loop for all the sheets using maybe Worksheets.Count and another dynamic loop for the rows ? The data always start every 6 rows eg( C12, C18 etc). Also I was thinking to define an integer representing the number of rows for the loop...

Any help is appreciated,
Thank you in advance

I tried to do this

Please Login or Register  to view this content.

But it does the renaming only for the first 2 values 01 and 02 and I don't know why...

Thanks for your reply. I got this :
Number of .csv
01
02
03
04

Number of .csv
05
06
07
08

Starting from C12 so C14 has the value 03

Yes it works, thank you very much