You are very welcome.

The worksheet uses VBA to help all the time, no reason the sheet can't help
VBA in return for once.
--
Gary's Student
gsnu200707


"lau_ash" wrote:

Now that's a creative solution.
That worked perfectly. Not quite the direction I was taking. But it
definately uses less code.
Thanks for the help!

"Gary''s Student" wrote:

How about:


Sub ref_bumper()

Set r = ActiveCell
Set h1 = Range("IV65535")
Set h2 = Range("IV65536")

h1.Formula = r.Formula
h1.Copy h2
r.Formula = h2.Formula

End Sub

This macro uses two helper cells. it:
1. moves the formula to the first helper cell - unchanged
2. copies the first helper to the cell just below it - increments the
reference
3. moves the new formula back to the Selected cell

If you don't like using the helper cells, you would have to get the formula,
somehow split it up, increment the reference part, put it back together,
replace the original.
--
Gary's Student
gsnu200706


"lau_ash" wrote:

Yes, I'm aware of that.
However, I don't want to offset "E4", E4 is referencing a cell on another
page, I want to off set that reference.

For example, the formula in E4 ='Main Sheet'!A258
I want a macro that will change it to ='Main Sheet'!A259
But it needs to be dynamic, such that it doesn't fill in a specifical A259,
but just increments by one cell.

"Gary''s Student" wrote:

Anytime you have Select'ed a specific cell with something like:
Range("E4").Select
you can move down to the next row with:

Selection.Offset(1,0).Select
--
Gary's Student
gsnu200706


"lau_ash" wrote:

I have a main sheet, and that references many other sheets with a specific
templet. Each templet sheet has a couple "special cells" that reference the
main page. Each row on the main page represents a sheet, and each column
represents these "special cells". What I need to do is have a macro that will
copy the last sheet, and make the "special cells"'s references/forumla
increment to next row on the main page.

I have the code that will copy the page, I just need to code to increment
the reference cells.

Sub Newsheet()

ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet

'Select Special Cell1
'For example, Range("E4").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[1] to ='Main
Sheet'!R[249]C[2]
'Select Special Cell2
'For example Range("F20").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[2] to ='Main
Sheet'!R[249]C[2]

End Sub

I've tired to make my question as clear as possible....hopefully this makes
sense.