You'll have to change this to use the right cells on the calculation sheet:

Option Explicit
Sub testme()

Dim InputWks As Worksheet
Dim CalcWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set InputWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

With InputWks
'headers in row 1
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With CalcWks
For Each myCell In myRng.Cells
'populate the CalcWks with values from the input sheet
.Range("a1").Value = myCell.Value
.Range("x99").value = mycell.offset(0,1).value
.range("iv323").value = mycell.offset(0,2).value

'do the calculation
Application.Calculate

'take some values back from the calcwks to the input sheet
myCell.Offset(0, 3).Value = .Range("b1").Value
myCell.Offset(0, 4).Value = .Range("c1").Value
myCell.Offset(0, 5).Value = .Range("d1").Value
Next myCell
End With

End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Khoshravan wrote:

I want to put the calculation in a separate tab and results in other one.
Is it possible to use relative reference for addressing between two tabs?

To explain completely, I want to get A1 value from Sheet1 as an input value,
take it to Sheet2, perform some calculations there and return the final
result of calculation as a single value to B1 in Sheet1 .

--

Dave Peterson