VBA Excel - Cell input from another worksheet

[mik-nik]

Hi, hope you can help?

I have an Excel spreadsheet where I am trying to import data from one sheet to another using option buttons, i.e.

Private Sub OB1_Click()
Range("J7").Value = Sheets("Sheet2").Range("G31").Value
End Sub

Private Sub OB2_Click()
Range("J7").Value = Sheets("Sheet2").Range("G32").Value
End Sub

Etc..

I know this doesn't work as you can't link directly from one sheet to another.

Any help would be great.

Excel 2002 SP2 VB 6.5

Thanks

Michael

 

[mik-nik]

Found the answer

Private Sub OptionButton1_Click()
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-3]C[-2]"
End Sub
Private Sub OptionButton2_Click()
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R[-2]C[-2]"
End Sub

Michael