Show value for cells in 'previous' sheets Closed

Question

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

venkat1926 · Answer

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.

Show value for cells in 'previous' sheets

1 response

Similar discussions

Subscribe To Our Newsletter!