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

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