Create list of non-contiguous cells and loop back to overwrite values

Hello All,

I'm a self taught VBA novice and I am in need of assistance. I am looking to make a list based on cell values in different sheets within the same workbook. I am also looking to create a loop when the list reaches, say 10 cells of data, to loop back to the first cell (Oldest value) and record a new value. Example: I have 4 sheets. Cell B2 in Sheet 1 has the word "Bear" and Cell B2 in Sheet 2 has the word "Ball". Sheet 3 Cells B2 and E2 have the words "Wall" and "Echo". Cells B2 and E2 in Sheet 4 have the words "Plant" and "Fin". How would I create a list in Sheet 5 of all those values? Secondly, how would I put a maximum on the sheet 5 range. So when it has all 6 values it continues to list them when they change but once it reaches row 10 it'll loop back to first cell and change that value. So if sheet 1 cell B2 changes to "Apple", Sheet 3 Cells B2 and E2 change to "Wolf" and "Guy", Sheet 4 Cells B2 and E2 now say "Pool" and "Blink" the data would look like:

Sheet 5 (Initially):
1 Bear
2 Ball
3 Wall
4 Echo
5 Plant
6 Fin
7 Apple
8 Wolf
9 Guy
10 Pool


Sheet 5 (After):
1 Blink
2 Ball
3 Wall
4 Echo
5 Plant
6 Fin
7 Apple
8 Wolf
9 Guy
10 Pool


The Range would be a set number (10 in this case) that would continue to update itself. I have somewhat managed to extract the values to create a list but I cant loop it back to overwrite the old values. Is this possible? Any help would be appreciated!