Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - Cell reference - (Range)
Hello, I am using Excel 2002 and I want a Macro to do a cell reference that referecence data from sheet 2 to sheet 1. For example in sheet 2 the data would look this: Sheet 2 A B C D E 1 John 16 18 19 30 2 Joe 22 33 44 55 3 Tom 6 88 99 10 4 Ann 2 99 22 44 Sheet 1 will not be populated at all until the macro is run. Sheet 1 - Without runing a macro A B C D E 1 2 3 4 Sheet 1 - After macro is run A B C D E 1 John 16 18 19 30 2 Joe 22 33 44 55 3 Tom 6 88 99 10 4 Ann 2 99 22 44 Now I know I can do a simple copy and paste, but the user will go back to sheet 2 and change the name in column A or change the amount in the other columns. The problem with that is Sheet 1 will not be updated and the user will forget to run the macro again. I perfer to do a cell reference. Is there a way I can do this? Any tips or website to go will be appreciated. Thank you in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - Cell reference - (Range)
why not use use referencing in the sheet. on sheet1, cell A1 has =Sheet2!A1
replicate this right/down. you could also set the change_event for sheet2 to copy the data to sheet1 Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("sheet1").Range(Target.Address) = Target.Value End Sub This is simplistic. You could amend to copy just the data. Lets say the table is in A1:E20 Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:E20")) Is Nothing Then Worksheets("sheet1").Range(Target.Address) = Target.Value End If End Sub "Alex Martinez" wrote: Hello, I am using Excel 2002 and I want a Macro to do a cell reference that referecence data from sheet 2 to sheet 1. For example in sheet 2 the data would look this: Sheet 2 A B C D E 1 John 16 18 19 30 2 Joe 22 33 44 55 3 Tom 6 88 99 10 4 Ann 2 99 22 44 Sheet 1 will not be populated at all until the macro is run. Sheet 1 - Without runing a macro A B C D E 1 2 3 4 Sheet 1 - After macro is run A B C D E 1 John 16 18 19 30 2 Joe 22 33 44 55 3 Tom 6 88 99 10 4 Ann 2 99 22 44 Now I know I can do a simple copy and paste, but the user will go back to sheet 2 and change the name in column A or change the amount in the other columns. The problem with that is Sheet 1 will not be updated and the user will forget to run the macro again. I perfer to do a cell reference. Is there a way I can do this? Any tips or website to go will be appreciated. Thank you in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference Range Names in Macro | Excel Discussion (Misc queries) | |||
Cell reference in a range | Excel Worksheet Functions | |||
Defining a range using a cell reference | Excel Worksheet Functions | |||
Cell Reference with Range Name | Excel Worksheet Functions | |||
use a cell to reference a range in a vlookup | Excel Discussion (Misc queries) |