I have a workbook that has weekly check register tabs. In the attached sample file I want cell H1 on Sheet 2 to pull the value in cell B1 of Sheet 1, with the hope that as I copy sheet 2 to sheet 3, etc it will automatically update cell h1 in each sheet. I tried putting the following in VBA, and then =PrevSheet(B1) in cell H1 of sheet 2, but obviously I'm doing something wrong because it's not working.
Function PrevSheet(rCell As Range)
Dim i As Integer
i = rCell.Cells(1).Parent.Index
PrevSheet = Sheets(i - 1).Range(rCell.Address)