Opening Another Workbook

[dani023]

Hello! I'm fairly new to VBA coding (and this site), and I'm having a little problem that I'm hoping someone can help me with. I'm trying to get my workbook to open up a second workbook that is saved on the user's desktop and copy information into that second workbook. I found code in a different thread on this site, and was able to tweak it to get it to work. The code works perfectly on my computer, but if I try to send the workbooks to another user it fails. I make sure the user has the workbooks saved to their desktop and documents to be safe. However, it still doesn't work. This is the code that I'm using:

Sub work()
 
Dim Vfile As Variant
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim wbFrom As Workbook
Dim wsFrom As Worksheet
 
 
 
 
Set wbFrom = ActiveWorkbook
Set wsFrom = Sheets("Log")
 
 
    '-------------------------------------------------------------
    'Open file with data to be copied
    
  Workbooks.Open Filename:=ThisWorkbook.Path & "\Workbook A.xlsm"
  
  Set wbTarget = Workbooks("Workbook A.xlsm")
    Set wsTarget = wbTarget.Worksheets("Survey")
 
    
   wsFrom.Range("A2").Copy
    wsTarget.Range("C2").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
   wsFrom.Range("C2").Copy
    wsTarget.Range("B2").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
    'Close file that was opened
    wbTarget.Close SaveChanges:=True
 
 
    
End Sub

The error the user receives gives a string of numbers, and then says that the file path is not found. Is there a way to get the above code to open a relative file name versus a fixed one? I have to send this file out to 10+ people, and would prefer not having to manually enter in each user's individual file path.

Any help that anyone could provide would be greatly appreciated.

Thank you!

 

[JLGWhiz]

This is most likely the culprit.

Workbooks.Open Filename:=[COLOR=#b22222]ThisWorkbook.Path [/COLOR]& "\Workbook A.xlsm"

The workbook the user is using to host the code could be saved to a default directory on a LAN or the Web. I suspect that since the error message number was a string of numbers it could be the web. If you make the path to open the second workbook equal the path where the file is stored then it should work OK.

Dim myPath As String
myPath = "C:\User\Desktop\"
Workbooks.Open myPath & "Workbook A.xlsm"