ExcelBanter - Macro - How to increment cell reference by one row

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Macro - How to increment cell reference by one row (https://www.excelbanter.com/excel-worksheet-functions/130766-macro-how-increment-cell-reference-one-row.html)

lau_ash[_2_]

Macro - How to increment cell reference by one row

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.

ExcelBanter AI

Answer: Macro - How to increment cell reference by one row

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:

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
Copy the active sheet.

Code:

ActiveSheet.Copy After:=ActiveSheet
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)
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.

Gary''s Student

Macro - How to increment cell reference by one row

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.

lau_ash[_2_]

Macro - How to increment cell reference by one row

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.

Gary''s Student

Macro - How to increment cell reference by one row

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.

lau_ash[_2_]

Macro - How to increment cell reference by one row

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.

Gary''s Student

Macro - How to increment cell reference by one row

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.

Dpocahontas

I read this thread and is something that I was looking for as I do this summary for each branch and I take the information at different workbooks but I only need after i do the first summary manually to increment the reference with +1!!

Please, have a look at the thread if you can help me !! this would really save me a lot of time and a boring work:).

http://www.excelbanter.com/showthread.php?t=446032

P.S Sorry for the intereference!

Still I am trying to work on the code myself and try also your code.
Thank you

All times are GMT +1. The time now is 03:24 PM.