Referring to "previous" worksheet

  • Thread starter Thread starter paulkaye
  • Start date Start date
P

paulkaye

Is there any way to refer to the "previous" worksheet rather than
having to provide a specific name? This would obviously be dependent
on the order of the worksheets being numerically listed somewhere but
I have no idea if this is the case.

Perhaps being more specific I may be able to tease another solution
from someone!:

I have one worksheet per month, labelled (currently) Jan-Dec. I want
to make a "brought forward" cell that refers to the last month's
total. However I want to be able to duplicate the sheet without having
to change the sheet name to which this cell refers.

I have one idea but can't really think how to achieve it: A title cell
in each sheet displays the worksheet name. I thought perhaps if I
formatted this cell cleverly I would be able to do some date
calculations to achieve what I want.

Any suggestions?!
 
Use the Index

MsgBox Worksheets(Activesheet.Index - 1).Name

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Use the Index

MsgBox Worksheets(Activesheet.Index - 1).Name

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...

What is that statement? Is it a formula or code? Do I put it in a cell
or formula? Sorry - I'm a bit of a noobie!
 
What is that statement? Is it a formula or code? Do I put it in a cell
or formula? Sorry - I'm a bit of a noobie!
Click to expand...

Also, how would I use it to refer to a cell within that sheet?
 
That is VBA. Are you looking for a worksheet solution?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
References to other sheets are always 'absolute' in Excel. If you put the
following VB function in a standard module you can use it like this in a
worksheet:

=SUM(PreviousSheetRef(A1:A4))

Function PreviousSheetRef(CellRef As Range) As Range
Dim PrevSheet As Worksheet
Application.Volatile
With Application.Caller
Set PrevSheet = .Parent.Parent.Sheets(.Parent.Index - 1)
Set PreviousSheetRef = PrevSheet.Range(CellRef.Address)
End With
End Function


--
Jim
| Is there any way to refer to the "previous" worksheet rather than
| having to provide a specific name? This would obviously be dependent
| on the order of the worksheets being numerically listed somewhere but
| I have no idea if this is the case.
|
| Perhaps being more specific I may be able to tease another solution
| from someone!:
|
| I have one worksheet per month, labelled (currently) Jan-Dec. I want
| to make a "brought forward" cell that refers to the last month's
| total. However I want to be able to duplicate the sheet without having
| to change the sheet name to which this cell refers.
|
| I have one idea but can't really think how to achieve it: A title cell
| in each sheet displays the worksheet name. I thought perhaps if I
| formatted this cell cleverly I would be able to do some date
| calculations to achieve what I want.
|
| Any suggestions?!
|
 
References to other sheets are always 'absolute' in Excel. If you put the
following VB function in a standard module you can use it like this in a
worksheet:

=SUM(PreviousSheetRef(A1:A4))

Function PreviousSheetRef(CellRef As Range) As Range
Dim PrevSheet As Worksheet
Application.Volatile
With Application.Caller
Set PrevSheet = .Parent.Parent.Sheets(.Parent.Index - 1)
Set PreviousSheetRef = PrevSheet.Range(CellRef.Address)
End With
End Function

--

| Is there any way to refer to the "previous" worksheet rather than
| having to provide a specific name? This would obviously be dependent
| on the order of the worksheets being numerically listed somewhere but
| I have no idea if this is the case.
|
| Perhaps being more specific I may be able to tease another solution
| from someone!:
|
| I have one worksheet per month, labelled (currently) Jan-Dec. I want
| to make a "brought forward" cell that refers to the last month's
| total. However I want to be able to duplicate the sheet without having
| to change the sheet name to which this cell refers.
|
| I have one idea but can't really think how to achieve it: A title cell
| in each sheet displays the worksheet name. I thought perhaps if I
| formatted this cell cleverly I would be able to do some date
| calculations to achieve what I want.
|
| Any suggestions?!
|
Click to expand...

Thank you all - I think I'm getting somewhere. I think I understand
that the first bit (=SUM...) should go in a cell and the second bit
(Function...) is VBA code that should go somewhere else. But where?! :)
 
Open the Visual Basic Editor (VBE) by pressing Alt-F11. From the Insert menu
pick Module. Paste my code in there and you can close the VBE and go back
to Excel.

This is a macro so your security settings may block this code from running.
Under Tools, Macros, Security you should set it to Medium so you can choose
to enable macros when this workbook opens.

--
Jim
| > References to other sheets are always 'absolute' in Excel. If you put
the
| > following VB function in a standard module you can use it like this in a
| > worksheet:
| >
| > =SUM(PreviousSheetRef(A1:A4))
| >
| > Function PreviousSheetRef(CellRef As Range) As Range
| > Dim PrevSheet As Worksheet
| > Application.Volatile
| > With Application.Caller
| > Set PrevSheet = .Parent.Parent.Sheets(.Parent.Index - 1)
| > Set PreviousSheetRef = PrevSheet.Range(CellRef.Address)
| > End With
| > End Function
| >
| > --
| >
| > | > | Is there any way to refer to the "previous" worksheet rather than
| > | having to provide a specific name? This would obviously be dependent
| > | on the order of the worksheets being numerically listed somewhere but
| > | I have no idea if this is the case.
| > |
| > | Perhaps being more specific I may be able to tease another solution
| > | from someone!:
| > |
| > | I have one worksheet per month, labelled (currently) Jan-Dec. I want
| > | to make a "brought forward" cell that refers to the last month's
| > | total. However I want to be able to duplicate the sheet without having
| > | to change the sheet name to which this cell refers.
| > |
| > | I have one idea but can't really think how to achieve it: A title cell
| > | in each sheet displays the worksheet name. I thought perhaps if I
| > | formatted this cell cleverly I would be able to do some date
| > | calculations to achieve what I want.
| > |
| > | Any suggestions?!
| > |
|
| Thank you all - I think I'm getting somewhere. I think I understand
| that the first bit (=SUM...) should go in a cell and the second bit
| (Function...) is VBA code that should go somewhere else. But where?! :)
|
 
Open the Visual Basic Editor (VBE) by pressing Alt-F11. From the Insert menu
pick Module. Paste my code in there and you can close the VBE and go back
to Excel.

This is a macro so your security settings may block this code from running.
Under Tools, Macros, Security you should set it to Medium so you can choose
to enable macros when this workbook opens.

--

| > References to other sheets are always 'absolute' in Excel. If you put
the
| > following VB function in a standard module you can use it like this in a
| > worksheet:
| >
| > =SUM(PreviousSheetRef(A1:A4))
| >
| > Function PreviousSheetRef(CellRef As Range) As Range
| > Dim PrevSheet As Worksheet
| > Application.Volatile
| > With Application.Caller
| > Set PrevSheet = .Parent.Parent.Sheets(.Parent.Index - 1)
| > Set PreviousSheetRef = PrevSheet.Range(CellRef.Address)
| > End With
| > End Function
| >
| > --
| >
| >| > | Is there any way to refer to the "previous" worksheet rather than
| > | having to provide a specific name? This would obviously be dependent
| > | on the order of the worksheets being numerically listed somewhere but
| > | I have no idea if this is the case.
| > |
| > | Perhaps being more specific I may be able to tease another solution
| > | from someone!:
| > |
| > | I have one worksheet per month, labelled (currently) Jan-Dec. I want
| > | to make a "brought forward" cell that refers to the last month's
| > | total. However I want to be able to duplicate the sheet without having
| > | to change the sheet name to which this cell refers.
| > |
| > | I have one idea but can't really think how to achieve it: A title cell
| > | in each sheet displays the worksheet name. I thought perhaps if I
| > | formatted this cell cleverly I would be able to do some date
| > | calculations to achieve what I want.
| > |
| > | Any suggestions?!
| > |
|
| Thank you all - I think I'm getting somewhere. I think I understand
| that the first bit (=SUM...) should go in a cell and the second bit
| (Function...) is VBA code that should go somewhere else. But where?! :)
|
Click to expand...

Brilliant - that's perfect! Thank you very much.

Paul
 
Back
Top