Forum Discussion

Trisa58's avatar
Trisa58
Copper Contributor
Oct 02, 2019

How to use PrevSheet

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

 

Formulas and Functions
Macros and VBA

1 Reply

  • PascalKTeam's avatar
    PascalKTeam
    Iron Contributor
    Oct 09, 2019

    Trisa58 

     

    Hi

    your VBA code is not correct but I've tried to set this up and it seems to work. Solutions in this https://kteamch-my.sharepoint.com/:x:/g/personal/pascal_kiefer_kteam_ch/EW8SX9eraLVGiab4yzyFvKMBaRRnL10ZWmaB5nT-Z9D9ow?e=ABDHfG&Download=1

     

    let me know if this worked for you

Resources