Macro for copy and paste as link

[robrobet]

Hi

I have this macro and want to change this to when it paste it paste special as link (link to another sheet ) not value,
Thanks
RR

Sub Test()
    Dim excel As excel.Application
    Dim wb As excel.Workbook
    Dim sht As excel.Worksheet
    Dim f As Object
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    f.Show
    Set excel = CreateObject("excel.Application")
    Set wb = excel.Workbooks.Open(f.SelectedItems(1))
    Set sht = wb.Worksheets("Master")
        sht.Range("A1:G10").Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = 0
    wb.Close Save
    End Sub

[playaller]

Hello. I removed the paste coding you had and added another method so try this.

Sub Test()
    Dim excel As excel.Application
    Dim wb As excel.Workbook
    Dim sht As excel.Worksheet
    Dim f As Object
    Dim Link As String 'Copy the link as text first
    
    Link = Range("A1") 'Will be used to convert to hyperlink in the end
    
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    f.Show
    Set excel = CreateObject("excel.Application")
    Set wb = excel.Workbooks.Open(f.SelectedItems(1))
    Set sht = wb.Worksheets("Master")
        sht.Range("A1:G10").Copy
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    'Convert the cell to a hyperlink
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Link _
        , TextToDisplay:=Link
    wb.Close Save
End Sub

[robrobet]

Thanks for your time,
But I want link as (C:\Documents and Settings\All Users) So when ever any value change on other sheet then my this sheet get update too. I do not want hyperlink.

[playaller]

Sorry, Just use the code below:

Sub Test2()
    Dim excel As excel.Application
    Dim wb As excel.Workbook
    Dim sht As excel.Worksheet
    Dim f As Object
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    f.Show
    Set excel = CreateObject("excel.Application")
    Set wb = excel.Workbooks.Open(f.SelectedItems(1))
    Set sht = wb.Worksheets("Master")
        sht.Range("A1:G10").Copy
    Range("A1").Select
    ActiveSheet.Paste Link:=True 'Paste data linked from the original sheet
    Application.CutCopyMode = False 'Unhighlight

    wb.Close Save
End Sub

[robrobet]

Thanks you it work,