Sheets(ActiveSheet.Index - 1).Range("a1")

l'esempio mostrerà il contenuto della cella a1 del foglio 2 nel caso tu ti trovi nel foglio3, la cella a1 del foglio 1 nel caso tu sia nel foglio 2 e così via. Ovviamente dovresti gestire il caso in cui tu sia già nel primo foglio.