Excel VBA copy cell value to find in another sheet the same value with hyperlink and click


I am Jira(one of the VBA Dummies) who is trying to learn VBA through Google search. I found this website very useful, Can anyone help me please?

I have a sheet with all the store locations and an email template. in the email template sheet I have a vlook up result will show in G25, I need to copy the value in G25(Ex. New Barrie) to find in another sheet (All Locations) the same value "New Barrie" with already set up hyperlink and click so that it will open a outlook new message. So far I came up with below code but of course I didn't know how to tweak the code so that it looks for the every value in G25 instead of the actual name that shows now (New Barrie). Hope someone can help me. Thanks so much!

Sub Email()

' Email Macro



Sheets("All Locations").Select

Cells.Find(What:="New Barrie", After:=ActiveCell, LookIn:= _

    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _

    xlNext, MatchCase:=False, SearchFormat:=False).Activate


Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Sheets("Email Template").Select

End Sub

Show source
| email   | vba   | macros   | copy-paste   | search   2016-11-12 16:11 1 Answers

Answers ( 1 )

  1. 2016-11-12 16:11

    I believe you're after something like follows:

    Option Explicit
    Sub Email()
        Dim val As String
        Dim myCell As Range
        val = Worksheets("template").Range("G25").Value '<--| store worksheet "template" cell "G25" value into a string variable
        Set myCell = Worksheets("All Locations").Cells.Find(What:=val, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False) '<--| set myCell to the one in "All Locations" worksheet whose content matches the stored value
        If Not myCell Is Nothing Then myCell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End Sub

    just review LookIn, LookAt and MatchCase arguments of Find() method to be sure they actually fits your need

◀ Go back