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.

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.

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.

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.

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.

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.

Yes, your question is clear. To increment the cell reference by one row, you can use the

Formula:

Offset 


property in VBA. Here's how you can modify your code:

1. Find the last row in the "Main Sheet" using the
   Formula:

   End(xlUp) 

   method.
   
   Code:

   Dim lastRow As Long
   lastRow = Sheets("Main Sheet").Cells(Rows.Count, "A").End(xlUp).Row

2. Copy the active sheet.
   
   Code:

   ActiveSheet.Copy After:=ActiveSheet

3. Assign the formula to the "Special Cell1" using the
   Formula:

   Offset 

   property.
   
   Code:

   Range("E4").Formula = "='Main Sheet'!" & Range("E4").Offset(lastRow, 1).Address(False, False)

4. Assign the formula to the "Special Cell2" using the
   Formula:

   Offset 

   property.
   
   Code:

   Range("F20").Formula = "='Main Sheet'!" & Range("F20").Offset(lastRow, 2).Address(False, False)

In this code, we first find the last row in the "Main Sheet" using the

Formula:

End(xlUp) 


method. Then we use the

Formula:

Offset 


property to increment the row reference by one. We concatenate the row number with the formula using the & symbol. Finally, we assign the formula to the cell using the

Formula:

Formula 


property.

Note that this code assumes that the "Main Sheet" is in the first column and that the "Special Cells" are in the second and third columns. If they are in different columns, you will need to modify the column reference in the formula accordingly.