Reference the previous Worksheet in a Formula
 

Hello
The following code is supposed to always reference the same cell in the
previous worksheet. Unfortunately, I can't figure out the sheet name with the
index number for the previous sheet. The line

ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"

doesn't work.

Any suggestions?

Dim WeekStep As Integer
Dim TotalSheets As Integer
Dim x As Integer

WeekStep = InputBox("Number of weeks per worksheet:")
TotalSheets = InputBox("Number of Worksheets:")

For x = 2 To TotalSheets
Sheets(x).Select
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"
Next x
--
Thanks very much. KDJ

Reference the previous Worksheet in a Formula
 

When you use the .FormulaR1C1 property, you are directly setting the cell's
formula to the text within the quotes. This means that any
variables/methods/properties used in VBA (such as x in this example). You
must convert these to text that makes sense in a worksheet formula, so:
ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"
becomes:
ActiveCell.FormulaR1C1 = "=" & Sheets(x-1).Name & "!RC+2"

Note also that you do not have to select a worksheet or cell in VBA before
you can manipulate it, so your code can be replace simply with :

For x = 2 To TotalSheets
Sheets(x).Range("A5").FormulaR1C1 = "=Sheets(x-1)!RC+2"
Next x

Cheers,
Dave

"KDJ" wrote:

Hello
The following code is supposed to always reference the same cell in the
previous worksheet. Unfortunately, I can't figure out the sheet name with the
index number for the previous sheet. The line

ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"

doesn't work.

Any suggestions?

Dim WeekStep As Integer
Dim TotalSheets As Integer
Dim x As Integer

WeekStep = InputBox("Number of weeks per worksheet:")
TotalSheets = InputBox("Number of Worksheets:")

For x = 2 To TotalSheets
Sheets(x).Select
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"
Next x
--
Thanks very much. KDJ

Reference the previous Worksheet in a Formula
 

Try

For x = 2 To TotalSheet
Sheets(x).Range("A5").FormulaR1C1 = "=" & Sheets(x - 1).Name & "!RC+2"
Next x

--
Jacob


"KDJ" wrote:

Hello
The following code is supposed to always reference the same cell in the
previous worksheet. Unfortunately, I can't figure out the sheet name with the
index number for the previous sheet. The line

ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"

doesn't work.

Any suggestions?

Dim WeekStep As Integer
Dim TotalSheets As Integer
Dim x As Integer

WeekStep = InputBox("Number of weeks per worksheet:")
TotalSheets = InputBox("Number of Worksheets:")

For x = 2 To TotalSheets
Sheets(x).Select
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"
Next x
--
Thanks very much. KDJ

Reference the previous Worksheet in a Formula
 

Thank you to both of you.
KDJ

"Jacob Skaria" wrote:

Try

For x = 2 To TotalSheet
Sheets(x).Range("A5").FormulaR1C1 = "=" & Sheets(x - 1).Name & "!RC+2"
Next x

--
Jacob


"KDJ" wrote:

Hello
The following code is supposed to always reference the same cell in the
previous worksheet. Unfortunately, I can't figure out the sheet name with the
index number for the previous sheet. The line

ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"

doesn't work.

Any suggestions?

Dim WeekStep As Integer
Dim TotalSheets As Integer
Dim x As Integer

WeekStep = InputBox("Number of weeks per worksheet:")
TotalSheets = InputBox("Number of Worksheets:")

For x = 2 To TotalSheets
Sheets(x).Select
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2"
Next x
--
Thanks very much. KDJ