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
|