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!

Rng(x) (where Rng is a Range and x is an integer) will reference the cell with an offset x-1 rows from Rng. Example: Range("A1")(5)=2 will put a 2 in cell A5.
Rng.offset(x) will reference a cell with an offset x rows from Rng. Example: Range("A1").Offset(3)=9 will put a 9 in cell A4.

Use either of those to access your list by changing x.

To prevent x from 'overflowing' you can create a variable for x and either keep incrementing it and then use the mod operator, or just keep incrementing it and then when it hits its overflow value, set it back to 0 or 1 (whichever is appropriate).

There are two tricks, though. One is to update the list when a cell changes. The other is to remember what the last cell was that changed.

The first problem is resolved by using the worksheet.change event. Google it to see how that is used.

The second problem should not be an issue if you allow the list to start at the same element every time the spreadsheet is opened (likely you would just start at the top). The 'x' variable used in your code will remember its value through the change events. Since you likely want x to be seen across modules, you will need to declare it as a global public variable (again, google for more details). If you do want it to remember where you left off writing in your list between the file being closed and open again, then you just need to store the x value in a cell somewhere in your workbook. When you start, you'd just get the value from that cell and use it - in fact, in this case, you would not use x as a variable, but would just increment, mod, and use that cell value as your offset.

Revisiting... Not sure if you resolved this or not, but with a global public variable, you would just keep incrementing 'x' and then have a check with something like
if x>20 then x=0