Hello Robb, bother yu again.......

Thanks Robb, it works for the column A of sheet 2, now I want to type a range of value from A2 to E2 of sheet 1, and these data should auto-formulated to sheet2 from column B2 to EZ2 range, How to do this?
Sorry that i am not good at programming.

Ian

Not sure whether you want to have the same formula in all the cells or if you want
to put something different in the rest of the cells in Row1. Also not sure whether
you would be filling the columns in order. Anyway, copy this in place of the previous
code - it should populate the same code in all cells in the range in any order you choose to fill them.
In other words Sheet2.EZ1 will contain =Sheet1.EZ1 etc.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet1")
If Not Application.Intersect(Target, .Range(.Columns(1), .Columns(156))) Is Nothing Then
ad = Target.Address
Worksheets("Sheet2").Cells(1, 1).Copy Destination:=Worksheets("Sheet2").Range(ad)
Target.Offset(1, 0).Select
Else
End If
End With
skip:
End Sub

Any good for you?

Regards

Thanks, and what if......................

Thanks, the following is an example of the workbook that I intent to make,

Sheet 1
A B C D E
1 100 200 300 400 500
2 200 300 400 500 600
3 300 400 500 600 700
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K

Sheet 2
A B EZ
1 =Sheet1!A1*Sheet1!B1 =Sheet1!A1*Sheet1!B1/Sheet1!E1 =A1+B1/E1
2 =Sheet1!A2*Sheet1!B2 =Sheet1!A2*Sheet1!B2/Sheet1!E2 =A2+B2/E2
3 =Sheet1!A3*Sheet1!B3 =Sheet1!A3*Sheet1!B3/Sheet1!E3 =A3+B3/E3
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K..

I want it as when I type in values at Sheet1 A4 B4 C4 D4 E4, the formula on sheet2 A4 ~ EZ4 would auto fill the formula format. Sheet1 has only 5 columns of data, and Sheet2 has 156 columns of formula data (A to EZ) which are all related to the 5 column data of sheet1. Is it possible to make a macro for this? Thanks again!

Thanks, the following is an example of the workbook that I intent to make,

Sheet 1
A B C D E
1 100 200 300 400 500
2 200 300 400 500 600
3 300 400 500 600 700
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K

Sheet 2
A B EZ
1 =Sheet1!A1*Sheet1!B1 =Sheet1!A1*Sheet1!B1/Sheet1!E1 =A1+B1/E1
2 =Sheet1!A2*Sheet1!B2 =Sheet1!A2*Sheet1!B2/Sheet1!E2 =A2+B2/E2
3 =Sheet1!A3*Sheet1!B3 =Sheet1!A3*Sheet1!B3/Sheet1!E3 =A3+B3/E3
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K

I want it as when I type in values at Sheet1 A4 B4 C4 D4 E4, the formula on sheet2 A4 ~ EZ4 would auto fill the formula format. Sheet1 has only 5 columns of data, and Sheet2 has 156 columns of formula data (A to EZ) which are all related to the 5 column data of sheet1. Is it possible to make a macro for this? Thanks again!

Thanks, the following is an example of the workbook that I intent to make,

Sheet 1
A B C D E
1 100 200 300 400 500
2 200 300 400 500 600
3 300 400 500 600 700
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K

Sheet 2
A B EZ
1 =Sheet1!A1*Sheet1!B1 =Sheet1!A1*Sheet1!B1/Sheet1!E1 =A1+B1/E1
2 =Sheet1!A2*Sheet1!B2 =Sheet1!A2*Sheet1!B2/Sheet1!E2 =A2+B2/E2
3 =Sheet1!A3*Sheet1!B3 =Sheet1!A3*Sheet1!B3/Sheet1!E3 =A3+B3/E3
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K

I want it as when I type in values at Sheet1 A4 B4 C4 D4 E4, the formula on sheet2 A4 ~ EZ4 would auto fill the formula format. Sheet1 has only 5 columns of data, and Sheet2 has 156 columns of formula data (A to EZ) which are all related to the 5 column data of sheet1. Is it possible to make a macro for this? Thanks again!