Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfering of data from 1 worksheet to another
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfering of data from 1 worksheet to another
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). 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. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfering of data from 1 worksheet to another
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 ------------------------------------------------ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfering a formula from one worksheet to another | Excel Worksheet Functions | |||
Transfering data from worksheet to another | Excel Discussion (Misc queries) | |||
shortcut for transfering info from one worksheet to another? | Excel Worksheet Functions | |||
Transfering a row of data to a template word or worksheet | Excel Discussion (Misc queries) | |||
Transfering data from 1 worksheet to another | Excel Discussion (Misc queries) |