Transfering of data from 1 worksheet to another

[nandosuperstar]

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.

 

[Dave Peterson]

Select the first worksheet and then ctrl-click on the sheet tabs for all the
other worksheets that get this.

Then select B2
Type this:
='Sheet 1'!g24
or
=if('Sheet 1'!g24="","",'Sheet 1'!g24)

Then rightclick on any of the grouped sheet tabs and choose Ungroup Sheets.

B2 in each of the selected sheets will have a formula that points back to Sheet
1 G24.

This is a nice way to do headers, but it's not a good way for data that will
move around (don't sort Sheet 1).

 

[smartin]

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 ------------------------------------------------