In a micro ...
I have given sample in the sheet 1. I generate random numbers on a ne
sheet say sheet 2. Then I insert a new sheet(sheet3) and calculate mea
from the data on sheet 2.
When I run the macro next time , random numbers are generated on th
sheet 4 , and on the sheet 5 it calculates the mean from the rando
numbers on sheet 2.
How is it possible that everytime I run a macro , it calculates th
mean from the random nubers on the previous sheet ?
TIA
rsank

--
rsank
-----------------------------------------------------------------------
rsankh's Profile: http://www.msusenet.com/member.php?userid=187
View this thread: http://www.msusenet.com/t-187051260

You need to provide the code you are using to generate the means - they are
referencing sheet 2 so this needs to change to dynamic values. Without your
code (or is it worksheet formula?) offering advice on chages is difficult.

--
Cheers
Nigel



"rsankh" wrote in message
...

In a micro ...
I have given sample in the sheet 1. I generate random numbers on a new
sheet say sheet 2. Then I insert a new sheet(sheet3) and calculate mean
from the data on sheet 2.
When I run the macro next time , random numbers are generated on the
sheet 4 , and on the sheet 5 it calculates the mean from the random
numbers on sheet 2.
How is it possible that everytime I run a macro , it calculates the
mean from the random nubers on the previous sheet ?
TIA
rsankh


--
rsankh
------------------------------------------------------------------------
rsankh's Profile: http://www.msusenet.com/member.php?userid=1879
View this thread: http://www.msusenet.com/t-1870512605

HERE IS THE CODE ...

Application.Run "ATPVBAEN.XLA!Random", "", 30, 2000, 7, , ActiveSheet
_
Range("$A$2:$B$6")
Sheets.Add
ActiveCell.FormulaR1C1 = "XBAR"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet4!R[-1]C:R[-1]C[29])"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A2000")
Type:= _
xlFillDefault
ActiveCell.Range("A1:A2000").Select

ActiveWindow.LargeScroll Down:=-68
ActiveCell.Offset(-1, 3).Range("A1").Select


---How to change the values of sheet nuber ?
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet4!R[-1]C:R[-1]C[29])

--
rsank
-----------------------------------------------------------------------
rsankh's Profile: http://www.msusenet.com/member.php?userid=187
View this thread: http://www.msusenet.com/t-187051260

The sheet number can be stored in a variable.....

Dim shNo as Integer
shNo = 4
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet" & shNo & "!R[-1]C:R[-1]C[29])"

Since the new sheet (added) is plus 1 you could use the count of sheets in
thew workbook and subtract 1.....

Dim shNo As Integer
shNo = ActiveWorkbook.Sheets.Count - 1
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet" & shNo & "!R[-1]C:R[-1]C[29])"



--
Cheers
Nigel



"rsankh" wrote in message
...

HERE IS THE CODE ...

Application.Run "ATPVBAEN.XLA!Random", "", 30, 2000, 7, , ActiveSheet.
_
Range("$A$2:$B$6")
Sheets.Add
ActiveCell.FormulaR1C1 = "XBAR"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet4!R[-1]C:R[-1]C[29])"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A2000"),
Type:= _
xlFillDefault
ActiveCell.Range("A1:A2000").Select

ActiveWindow.LargeScroll Down:=-68
ActiveCell.Offset(-1, 3).Range("A1").Select


---How to change the values of sheet nuber ?
ActiveCell.FormulaR1C1 = "=AVERAGE(Sheet4!R[-1]C:R[-1]C[29])"


--
rsankh
------------------------------------------------------------------------
rsankh's Profile: http://www.msusenet.com/member.php?userid=1879
View this thread: http://www.msusenet.com/t-1870512605