Reference the previous Worksheet in a Formula

[KDJ]

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

 

[Dave Ramage]

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

 

[Jacob Skaria]

Try

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

 

[KDJ]

Thank you to both of you.
KDJ

Try

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