How to use PrevSheet

[Trisa58]

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.

Suggestions please.

Function PrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function

[#2]

@Trisa58 

Hi

your VBA code is not correct but I've tried to set this up and it seems to work. Solutions in this file

let me know if this worked for you