Move formula reference one cell down

[mrdomer07]

I have a cell, B2, which refers to another sheet in my file. The formula reference to the other sheet is "=ESPN!S6". Based on the value returned to B2, it generates a URL which forms the basis of a macro. I would like to write a macro which moves the referenced cell of B2 down by one cell to "=ESPN!S7" so that the value of B2 -> the URL -> the macro all update accordingly. Ultimately I want to shift the referenced cell down one cell at a time a few hundred times until I get to the bottom of the list in column S of the ESPN sheet.

Is there a simple way to automate this in VBA?

[FDibbins]

You show your formula as "=ESPN!S6"
Does it actually contain "" or are you just showing that?
If it does, try removing so you can copy down...
=ESPN!S6

[mrdomer07]

You show your formula as "=ESPN!S6"
Does it actually contain "" or are you just showing that?
If it does, try removing so you can copy down...
=ESPN!S6

It is not in quotes - please excuse the poor formatting. I do not want to copy it down because 1) I want to keep the formula in B2 and 2) I want to do this through a macro.

Edit: I am thinking that I can use the indirect function instead of directly referencing ESPN!S6. I could use a macro to increment the 6 to a 7 the. Have the indirect function reference the cell holding the 6 or 7... I just signed off for the night but will report back tomorrow.

[mrdomer07]

I ended up using the indirect function which worked much better.