Solved: Equations using adjacent worksheets

[S_OB]

Hi,

Can someone help me on a VBA problem I having. I have a workbook with quite number of worksheets and I'd like to perform calculations on some of my worksheets using cells from that are before and after the worksheet. This has proved surprisingly difficult to do. Does someone have a method for reading the names of previous and next worksheets and then putting cells into an equation?

For example I have worksheets in say a workbook colour : Red, Green, Blue, Orange , Brown.

For worksheet Green I want to automatically formulate an equation like Red!A1 + Blue!A1. For worksheet Blue an equation like Green!A1 +Orange!A1 and so on.


If anyone can help me out that would be great!


S_OB

[TheAntiGates]

sheets(ActiveSheet.previous.name).range("a3") = 1
sheets(ActiveSheet.next.name).range("a2") = 2

myVariable = sheets(ActiveSheet.previous.name).range("a1") + sheets(ActiveSheet.next.name).range("a1")

[S_OB]

Great. That worked! But is there a way of setting up the code so a formula is shown in Excel e.g Blue!A1 + Red!A1? And just out of interest is there a fairly simple way of involving sheets which are not the previous or next sheet?

Cheers!