Home

How to use PrevSheet

%3CLINGO-SUB%20id%3D%22lingo-sub-887668%22%20slang%3D%22en-US%22%3EHow%20to%20use%20PrevSheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887668%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20that%20has%20weekly%20check%20register%20tabs.%26nbsp%3B%20In%20the%20attached%20sample%20file%20I%20want%20cell%20H1%20on%20Sheet%202%20to%20pull%20the%20value%20in%20cell%20B1%20of%20Sheet%201%2C%20with%20the%20hope%20that%20as%20I%20copy%20sheet%202%20to%20sheet%203%2C%20etc%20it%20will%20automatically%20update%20cell%20h1%20in%20each%20sheet.%26nbsp%3B%20I%20tried%20putting%20the%20following%20in%20VBA%2C%20and%20then%20%3DPrevSheet(B1)%20in%20cell%20H1%20of%20sheet%202%2C%20but%20obviously%20I'm%20doing%20something%20wrong%20because%20it's%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuggestions%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EFunction%20PrevSheet(rCell%20As%20Range)%0A%20%20%20%20Application.Volatile%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%20rCell.Cells(1).Parent.Index%0A%20%20%20%20PrevSheet%20%3D%20Sheets(i%20-%201).Range(rCell.Address)%0AEnd%20Function%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-887668%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901385%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20PrevSheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F328864%22%20target%3D%22_blank%22%3E%40Trisa58%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3Eyour%20VBA%20code%20is%20not%20correct%20but%20I've%20tried%20to%20set%20this%20up%20and%20it%20seems%20to%20work.%20Solutions%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Fkteamch-my.sharepoint.com%2F%3Ax%3A%2Fg%2Fpersonal%2Fpascal_kiefer_kteam_ch%2FEW8SX9eraLVGiab4yzyFvKMBaRRnL10ZWmaB5nT-Z9D9ow%3Fe%3DABDHfG%26amp%3BDownload%3D1%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Efile%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20me%20know%20if%20this%20worked%20for%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E
Trisa58
Occasional Contributor

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

 

1 Reply

@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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies