Finding the active cell in other work book

[msahmed]

hi team,

In my work, I have to copy the particular cells of active sheet (say Workbook 1), find in other workbook(say workbook 2)

It is very time consuming to do this for every cell in the workbook 1

I need a VBA code to assign a short cut Key, may be "Ctrl + E", to find the active cell in other work book

If you think there is a better way of doing this, please let me know!

Thanks in advance

[offthelip]

Try this
Assuming you are in Book2 and the other book is book3
[VBA]Dim tempadd As String


Workbooks("Book3").Activate
tempadd = ActiveCell.Address
Workbooks("Book2").Activate
MsgBox (tempadd)


[/VBA]

Note this appears to me to answer what you asked, but you haven't stated what you want to do with the address once you have found it.

[msahmed]

Thanx for the help but need some clarification...

What wborkbook does "Book2" & "Book3" refers here...?

like i need to find "E7" of Book 2 in Book3

and also assign short cut key "Ctrl + E"

Thanx

[offthelip]

Book2 is the name of the workbook you are currently in, Book3 in the name of the "other" workbook. I don't know the names of your workbooks so I used the excel default names.vthese could be the "Workbook 1" and "workbook 2" from you original question.

What do you mean by "Find"

[msahmed]

"Ctrl + f" find command

The whole thing is go to the active cell value in other work book not to get cell address.

Ex:

When I recorded the process it shows the code as


Sub Macro1()

Range("E12").Select
Selection.Copy
Windows("Book3.xlsx").Activate
Cells.Find(What:="Tar pore", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
End Sub



The problem here is the macro sets "E12" as range where as i want the active cell of Book2 to be the range and also
the find "What:="Tar pore" should also be the active cell value of Book2.

Hope you understood

[offthelip]

That is much clearer :
try this;

[vba]Sub test()
Dim findstr As String


findstr = ActiveCell.Value


Workbooks("book3").Activate
Cells.Find(What:=findstr, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
End Sub
[/vba]

I forgot to say to assign a control key to the macro go to macros on the tab then select this macro and then options and asign the control key

[msahmed]

That works great...:-)

Thanks again...

[msahmed]

Hi there,

Is there any way with copied value to get find. Like if i copied the data from other source, say pdf.

[offthelip]

Try this instead of the activecell value: ( I haven't tested it) also you will need to add a reference to Microsoft Forms 2.0. Object library in the VBA refences, ( go to Tools references inthe VBa windos and click the refence as required

[vba] Dim objData As New MSForms.DataObject
Dim Findstr as string


objData.GetFromClipboard
findstr = objData.GetText()


[/vba]