nandosuperstar wrote:
Is it possible to copy a figure from a cell over to a new worksheet without
having to do it manually over every sheet?
i.e i want the figure in cell g24 in sheet 1 to appear in cell b2 in sheet
2,and the figure in cell g24 in sheet 2 to appear in cell b2 in sheet 3 etc
etc.

Here are two macros that do what you ask.

CopyL2R copies G24 and pastes the formula into B2 in the sheet on the
right, and so on.

CopyR2L copies G24 and pastes the formula into B2 in the sheet on the
left, and so on.

To use, open the VBE (press F11), locate and select your workbook in the
"project" dialog, Insert | Module, and paste the code below in the white
space that opens up. Then return to your workbook (F11 or Alt+Q), press
Alt+F8 to open the Macro dialog, select the appropriate macro, and Run.


' BEGIN CODE ----------------------------------------------

Sub CopyL2R()
Dim i As Long
Dim OldName As String
OldName = Worksheets(1).Name
i = 2
While i <= Worksheets.Count
Worksheets(i).Activate
Range("B2").Formula = Range(OldName & "!G24").Formula
OldName = Worksheets(i).Name
i = i + 1
Wend
End Sub

Sub CopyR2L()
Dim i As Long
Dim OldName As String
OldName = Worksheets(Worksheets.Count).Name
i = Worksheets.Count - 1
While i = 1
Worksheets(i).Activate
Range("B2").Formula = Range(OldName & "!G24").Formula
OldName = Worksheets(i).Name
i = i - 1
Wend
End Sub

' END CODE ------------------------------------------------