relative reference between two tabs

[Khoshravan]

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 .

 

[Rick Rothstein]

I'm not sure what you mean by performing some calculation on Sheet2. If you
are talking VB code (which is the main item discussed here in this
excel.programming newsgroup), why not perform the calculation in VB? And if
you are talking worksheet formulas, whatever calculation you are performing
on Sheet2 could be done in Sheet1 just as easily. Perhaps if you provide
more detail about your layout and what you are trying to do with it, maybe
things would be clearer.

 

[Dave Peterson]

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 here:
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]

In am talking about recording a Macro. In this MAcro I want to get input data
from one sheet and perform calculation in another sheet.
Also I want to know if relative reference works in this situation (between
two sheets) or not.