Look a the value of the current cell on the other worksheet

[ermccarthy]

I have written a small macro that I want it to look the value of the Active cell from one workbook on a second workbook, but I am have no luck......This is what I have, if it leave my value just to say Active cell, when you switch windows, the value changes. But I can't seem to get it to work using the following command either. What am I doing wrong??

exch = Windows("Rates").ActiveCell.Value
Windows("GLEIS.DBF").Activate
Range("A2").Select
Do Until ActiveCell = (exch)
ActiveCell.Offset(1, 0).Select
Loop

[s-o-s]

Would this do the trick?

exch = Windows("Rates").ActiveCell.Address
Windows("GLEIS.DBF").Activate
Range(exch).Select

[ermccarthy]

When I run this it give me a runtime error 9: Subscript out of range. The debugger highlights the exch= line.

Any Ideas?

[s-o-s]

I just copied and pasted your version without looking closely enough...you were using another workbook I supplied the code for a range call. The code below works for standard sheet names change as needed.

Sub Test_Me()
exch = Windows("Book1").ActiveCell.Address
Windows("Book2").Activate
Range(exch).Select
End Sub

[Al Chara]

Ever thought of using the INDIRECT function?

[ermccarthy]

exch = Windows("Rates").ActiveCell.Address
Windows("GLEIS.DBF").Activate
Range(exch).Select


This is the formula I have, but it still gives me the same runtime error....I don't know what I am doing wrong....

[Al Chara]

You need an extension on Rates:

exch = Windows("Rates.xls").ActiveCell.Address

[ermccarthy]

OK GREAT!!!! Almost there....When I do this puts me at the same Address of the Rate.xls Active Cell, but what I am trying to accomplish is to search for the Value of the Active Cell on the Rate sheet say 950 which is located somewhere in Column A of the 2nd file. Any Ideas??

[Al Chara]

Try the following:
With Workbooks("GLEIS.xls").Worksheets("Sheet1").Columns(1)
Set c = .Find(Windows("Rates.xls").ActiveCell)
End With
Windows("GLEIS.xls").Activate
ActiveSheet.Range(c.Address).Select

replace GLEIS.xls with GLEIS.DBF or whatever.