The only way I know of doing this is to use VBA
Option Explicit
Sub Sheet1to2()
Dim w1 As Worksheet, w2 As Worksheet
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("sheet2")
Dim c As Range
Dim rng As Range
Set rng = w1.Range("D7:F9")
Dim cAddress As String
For Each c In rng
If c = 10 Then
cAddress = c.Address
'MsgBox cAddress
w2.Range(cAddress) = 10
End If
Next c
MsgBox "complete"
End Sub
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
Bookmarks