Referring to cell in previous sheet

[timhillyard]

Hello,

I create a new worksheet on a weekly basis. On the most recent worksheet in cell A1, I will enter a number. In cell A2, I would like a formula that calculates the difference between cell A1 and cell A1 of the previous sheet.

As I am always making a new sheet, I would like a formula that doesn't involve me having to manually enter a different sheet name into the formula bar every time.

I have looked over many forums on the internet, suggesting Visual Basic codes. However, whenever I try and use them, I get the '#name?' reference. I am wondering if I am using Visual Basic slightly wrong, which is why I am getting an error, because other people with this problem seem to have had their issue solved.

Regards,

Tim

 

[GlennUK]

Show the code that you tried to use, and tell us all where you placed it.

 

[timhillyard]

Well I have tried a few different codes. This is an example of one:

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

In Visual Basic, I would double-click 'This Workbook' within Microsoft Excel Objects, and then just paste the code into the window.

And then in Cell A2, I would enter =PrevSheet(A1)-A1

It just comes back with an error. Any ideas?

 

[GlennUK]

Insert a module, and put the code in there instead of in the Workbook code area.

 

[timhillyard]

Haha yeah that works now. What a straight-forward solution. Been trying to solve that for ages.

Thanks for your help!