Previous Topic Next Topic

Show value for cells in 'previous' sheets

Closed
Ciaran - Sep 26, 2009 at 07:48 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 26, 2009 at 09:23 PM
Hello,

I would like to show the value for specified cells in a 'previous' sheet. However I would like to be able to copy and paste the formula throughout 400 sheets so I need to know exactly how to do this if it is possible of course.

Basically it would be the same result as putting in a formula '=Sheet1!A1' (when you are on sheet 2 A1). But if I copy and paste this formula into say sheet 3 A1 then it will still be showing the value of sheet 1 A1 when if fact I want it to show sheet2 A1 because it is the previous sheet to sheet 3.

I hope I'm explaining this ok.

Many thanks for your help.

Regards,
Ciaran

1 reply

Answer 1 / 1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 26, 2009 at 09:23 PM
try this macro(Modify to suit you) 

Sub test()
Dim j As Integer, k As Integer
j = Worksheets.Count
'MsgBox j
For k = 2 To j
Worksheets(k).Activate
'MsgBox ActiveSheet.Name
Range("B1") = ActiveSheet.Previous.Range("A1")
Next k
End Sub


remember activesheet.previous will give error if the active sheet is the first sheet because there is no previous sheet.
0