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
Configuration: Windows Vista Internet Explorer 7.0
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. |
"if cell.valuetype" Create function find cell return value in adjacent cell Every time you enter a value into a cell in the worksheet, excel automatically Excel "value from cell" as reference Excel functions name sheet on cell sum('sheet1'! Excel if cell from sheet 1 equals 1 then execute sheet 2 Excel macro compare 2 cells on separate sheets Excel macro show cell 5 and 7 Excel place value on cell Excel sumif one cell value is different than another Lookup formula to return multiple values in one cell One cell automatically generates another cells value excel Return data from a cell value vba Start view show desktop icons registry value Sum the value of cells vba Using vlookup when have two cells with same value Vba copy cells to sheet Vba macros copy cells to different sheet if meets criteria