 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Referring to "previous" worksheet
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  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Is there any way to refer to the "previous" worksheet rather than > having to provide a specific name? This would obviously be dependent [quoted text clipped - 15 lines] > > Any suggestions?! > Use the Index > [quoted text clipped - 27 lines] > > > Any suggestions?! 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! > > Use the Index > [quoted text clipped - 34 lines] > 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! Also, how would I use it to refer to a cell within that sheet? That is VBA. Are you looking for a worksheet solution? SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> > Use the Index >> [quoted text clipped - 39 lines] > > Also, how would I use it to refer to a cell within that sheet? 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 SignatureJim | Is there any way to refer to the "previous" worksheet rather than | having to provide a specific name? This would obviously be dependent [quoted text clipped - 15 lines] | | 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 [quoted text clipped - 31 lines] > | > | 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. SignatureJim | > 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 [quoted text clipped - 35 lines] | 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 [quoted text clipped - 45 lines] > | 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?! :) Brilliant - that's perfect! Thank you very much. Paul
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.