Using VBA to Open A Changing File Name




I am writing a code that will open a SharePoint path, I will select the file I want to open (the name changes). That is where I get stuck, I want the code to copy a range from the changing name file and paste it into another set file name. I just don't know how to determine what the code is for the changing file name and how to get the code to continue once I open it.


In red is where I need the code to go for the changing file name. It needs to continue once I open the file. I have used this code in the past, but it was with a C: Drive file and it continued once I opened the file.

This is what I have (I changed the name of the SharePoint file as it is a work file):


Sub Audit_Sheet()
' Audit_Sheet_Data Macro

Sheets("Audit Sheets").Select

ActiveWorkbook.FollowHyperlink Address:="SharePoint.file"
filetoopen = ThisWorkbook.Worksheets("Audit Sheets").Range("A1")

Sheets("Audit Sheets").Select

Windows("Voice and Season Training - Official.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

MsgBox ("Run Complete")

End Sub


Thanks in advance.

0 Replies