Reference to previous sheet VBA question

Hi all,

I am currently using a quick macro in order to reference the previous sheet in excel documents. I’m using it to display trends week by week. (UP DOWN STEADY based on the previous sheet, looks like this =IF(prevsheet(I33)>I33,”#”,IF(prevsheet(I33)<I33, “$”, IF(prevsheet(I33)=I33,”1″))) I am running into a small problem though, in which if i have two excel workbooks open at the same time my trends will change and they will be incorrect, up until i close the second workbook. My macro looks like this:

Function PrevSheet(RCell As Range)

    Dim xIndex As Long

    Application.Volatile

    xIndex = RCell.Worksheet.Index

    If xIndex > 1 Then _

        PrevSheet = Worksheets(xIndex – 1).Range(RCell.Address)

End Function

 

 

So i’m looking on how to lock it only to run on the excel workbook that it’s intended for and not read from another workbook. Should I include something like a filename?

By: usif

Leave a Reply

Your email address will not be published. Required fields are marked *