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

Question

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

Range("G25").Select

Selection.Copy

Sheets("All Locations").Select

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

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

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

Range("A37").Select

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 to Excel VBA copy cell value to find in another sheet the same value with hyperlink and click ( 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

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

◀ Go back